Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Rick C. Hodgin
  Where is Rick C. Hodgin?
 Indianapolis
 Indiana - United States
 Rick C. Hodgin
 To: prasanna kunder
  Where is prasanna kunder?
 mumbai
 India
 prasanna kunder
 Tags
Subject: RE: How To Do
Thread ID: 365534 Message ID: 365543 # Views: 55 # Ratings: 1
Version: Visual FoxPro 9 Category: Grids
Date: Thursday, December 27, 2012 9:49:22 PM         
   


> >
> >
> >
> > If those are the only three descriptions you'll ever hvae, then something simple like:
> >
> > SELECT flatNo, name, ;
> >        SUM(IIF(desc = "sinking fund", amt, 00000.00)) AS nSink, ;
> >        SUM(IIF(desc = "repairs & ma", amt, 00000.00)) AS nMaint, ;
> >        SUM(IIF(desc = "water charge", amt, 00000.00)) AS nWater ;
> >     FROM myTable ;
> >     INTO CURSOR c_output ;
> >     GROUP BY 1, 2
> > 

> >
> > If you have an unknown number of descriptions, then there are a few ways to extract them into vertical rows and then convert to horizontal columns. Anders will be on shortly to show you the crosstab method. :-)
> >
> > Best regards,
> > Rick C. Hodgin
>
>
> sir
> i have unknown number of desc.., that's not by me, by users,
> now just trying :- Do (_Genxtab) With 'xtab',.T.
>
> thanks again



Something like:
* Grab the unique listing
SELECT DISTINCT desc ;
    FROM myTable ;
    INTO CURSOR cDescList

* Build it (if it gets to be too big, you'll have to do it programmatically)
lcSql = "SELECT flatNo,name,"
SCAN
    lcSql = lcSql + ",SUM(IIF(desc = '" + cDescList.desc + "', amt, 00000.00)) as amt" + ALLTRIM(STR(RECNO('cDescList'),3,0))
ENDSCAN

* Append the tail
lcSql = lcSql + " FROM myTable GROUP BY 1, 2 INTO CURSOR c_output"

* Execute it
&lcSql


Best regards,
Rick C. Hodgin

ENTIRE THREAD

How To Do Posted by prasanna kunder @ 12/27/2012 8:08:28 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 9:21:35 PM
RE: How To Do Posted by prasanna kunder @ 12/27/2012 9:28:20 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 9:38:18 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 9:49:22 PM
RE: How To Do Posted by prasanna kunder @ 12/27/2012 9:58:56 PM
RE: How To Do Posted by Rick Hodgin @ 12/27/2012 10:14:17 PM
RE: How To Do Posted by Anders Altberg @ 12/28/2012 2:21:03 AM
RE: How To Do Posted by prasanna kunder @ 12/29/2012 10:41:30 AM