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: 365539 # Views: 87 # Ratings: 2
Version: Visual FoxPro 9 Category: Grids
Date: Thursday, December 27, 2012 9:21:35 PM         
   


> Hello Experts
>
> I have table like this
>
>
FlatNo   Name  Desc                        amt  
> 201      xyz   sinking fund                 50.00
> 201      xyz   repairs & maintenance fund  100.00
> 201      xyz   water charges                75.00 
> 202      lmn   sinking fund                 50.00
> 202      lmn   repairs & maintenance fund  120.00
> 202      lmn   water charges                75.00 
> 203      abc   sinking fund                 40.00
> 203      abc   repairs & maintenance fund  110.00
> 203      abc   water charges                65.00 
> 
> 
> now i want to fit in grid
> 
> FlatNo   Name   sinking fund repairs & maintenance fund water charges
> 201      xyz           50.00           100.00                75.00
> 202      lmn           50.00           120.00                75.00
> 203      abc           40.00           110.00                65.00
> 
> how to do

>
> pl.guide me
> thanks in advance



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

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