Querying Multiple Rows into One Row

[jmueller]

Hi everybody,

Can someone please tell me the syntax using a SELECT statement to do the following (if there is one, of course):

I have the following data:

Year Customer# TotalSales
2003 ABC $400
2004 ABC $800

2003 DEF $500

2004 GHI $600

Now, I want to consolidate the above into a table that has 1 record per Customer... as such:

Customer # TotalSales_2003 TotalSales_2004
ABC $400 $800
DEF $500 $0
GHI $0 $600

Is this possible?

Thank You!

Jack.

 

[Stella740pl]

One of the ways:

SELECT customer, ;
SUM(IIF(year=2003, TotalSales, 000000)) AS Tot2003, ;
SUM(IIF(year=2004, TotalSales, 000000)) AS Tot2004 ;
FROM MyTbl ;
GROUP BY 1 ORDER BY 1 ;
INTO CURSOR AllSales

 

[swguynos]

Use the Xtab program to generate what you want.

 

[jmueller]

Thanks for your replies. I think for the sake of time & effort, i'll go with Stella's. The XTAB stuff just seems really complicated. I tried using the wizard but was not having any luck. Is there somewhere I can get some "simple" sample code that shows how I would program this using gsXtab1 or VfpXtab programs?

Thanks again.

Jack.

 

[Dan Freeman]

Not much to it. You need a cursor with 3 columns. First column is the left-most column for output, second column is the heading values, 3rd column is the intersection data between row label/column heading.

Substitute your field names:

Select Customer, Year, sales ;
into cursor temp ;
order by 1,2,3 ;
group by 1,2

DO (_genxtab)

The best documentation for xtab is in the comments at the top of vfpxtab.prg.

 

[jmueller]

Thanks Dan,

I will check it out this weekend. You make it sound so simple that I fear it can't be true... but i'll see.