insert 2-D pivot table into a regular recordset ?

cs001 · November 16th, 2003, 11:39 PM

How can I insert pivot table data into a regular ado recordset?

The pivot table would have just to dimensions.

Currently, we use ADO pass through SQL on an Access97 database, which works
fine, but we want to move it to SQL Server 2000.

For example:
source table:
[Country], [Amount Raised], [Date]
Germany $100 2001
Germany $100 2000
France $120 2000

pivot table:
2000 2001
Germany $100 $100
France $100 $0

(Sorry if the pivot table formatting is off )

Thanks in advance for your help,

Eric

Jeff Mason · November 17th, 2003, 08:16 AM

This is the one area where Access does it better than SQL Server. SQL Server does not provide the TRANSFORM...PIVOT command (which is not SQL Standard). To do this in standard SQL requires use of the CASE expression, as:

Code:

SELECT Country,
    SUM(CASE WHEN [Date]='2000' THEN [Amount Raised] ELSE 0 END) as Amount2000,
    SUM(CASE WHEN [Date]='2001' THEN [Amount Raised] ELSE 0 END) as Amount2001
FROM yourTable
GROUP BY Country

P.S. Don't use "Date" as a column name. It's just not a helpful name (what date?). (especially when your data values aren't a date) :)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com