Paul Nielsen
pauln at sqlserverbible.com
Sat Feb 2 11:26:45 CST 2008
The case expression method is a easier than the pivot method and both generate the same query execution plan. This chapter code has examples of both in the code. If you want the text, the aggregating data is the sample chapter - you can download it from my site. SELECT Category, SUM(Case Region WHEN 'South' THEN Amount ELSE 0 END) AS South, SUM(Case Region WHEN 'NorthEast' THEN Amount ELSE 0 END) AS NorthEast, SUM(Case Region WHEN 'MidWest' THEN Amount ELSE 0 END) AS MidWest, SUM(Case Region WHEN 'West' THEN Amount ELSE 0 END) AS West, SUM(Amount) as Total FROM RawData GROUP BY Category ORDER BY Category The complexity you have is that you want to group by a range. If you give me some DDL and sample data, I'll help you build a subquery to put the data in ranges. -Paul www.sqlserverbible.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, February 02, 2008 9:48 AM To: 'Access Developers discussion and problem solving'; 'Discussion concerning MS SQL Server' Subject: [dba-SQLServer] SQL Server Pivot I have a bunch of columns that break out ages into bands. For example: Presence_of_adults_age_65_74_specific Presence_of_adults_age_55_64_specific Presence_of_adults_age_45_54_specific etc Each column has codes 1,2,3 I need counts of each column, for each value column Cnt1 Cnt2 Cnt3 65_74 45 3 23 55_64 103 48 211 45_54 20 1 49 etc I think the Pivot statement is going to do that for me but I can't seem to wrap my mind around the syntax. Can anyone point me to a web page that clearly explains this or simply write a SQL statement that does this? Thanks John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com __________ NOD32 2845 (20080202) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com