[dba-SQLServer] SQL Server Pivot

USE TempDB

CREATE TABLE AgeCount (
  Presence_of_adults_age_65_74_specific INT,
  Presence_of_adults_age_55_64_specific INT,
  Presence_of_adults_age_45_54_specific INT
  )
go

INSERT AgeCount VALUES (1 ,2 ,3 )
INSERT AgeCount VALUES (1 ,3 ,3 )
INSERT AgeCount VALUES (1 ,2 ,2 )
INSERT AgeCount VALUES (1 ,2 ,1 )
INSERT AgeCount VALUES (2 ,1 ,2 )
INSERT AgeCount VALUES (1 ,3 ,1 )
INSERT AgeCount VALUES (2 ,2 ,2 )
INSERT AgeCount VALUES (3 ,3 ,1 )
INSERT AgeCount VALUES (2 ,2 ,1 )
INSERT AgeCount VALUES (1 ,3 ,1 )
INSERT AgeCount VALUES (2 ,3 ,1 )

-- Case Expression Method
SELECT AgeRange, 
  SUM(Case Code WHEN 1 THEN Cnt ELSE 0 END) AS Cnt1,
  SUM(Case Code WHEN 2 THEN Cnt ELSE 0 END) AS Cnt2,
  SUM(Case Code WHEN 3 THEN Cnt ELSE 0 END) AS Cnt3
  FROM 
   (SELECT '65_74' as AgeRange, Presence_of_adults_age_65_74_specific as
Code, Count(*) as Cnt
      FROM AgeCount
      GROUP BY Presence_of_adults_age_65_74_specific
    union
    SELECT '55_64', Presence_of_adults_age_55_64_specific, Count(*)
      FROM AgeCount
      GROUP BY Presence_of_adults_age_55_64_specific
    union
    SELECT '45_54', Presence_of_adults_age_45_54_specific, Count(*)
      FROM AgeCount
      GROUP BY Presence_of_adults_age_45_54_specific) as Normalized
  GROUP BY AgeRange
  ORDER BY AgeRange

-- Pivot Method
SELECT AgeRange, [1] AS Cnt1, [2] AS Cnt2, [3] AS Cnt3
  FROM 
   (SELECT '65_74' as AgeRange, Presence_of_adults_age_65_74_specific as
Code, Count(*) as Cnt
      FROM AgeCount
      GROUP BY Presence_of_adults_age_65_74_specific
    union
    SELECT '55_64', Presence_of_adults_age_55_64_specific, Count(*)
      FROM AgeCount
      GROUP BY Presence_of_adults_age_55_64_specific
    union
    SELECT '45_54', Presence_of_adults_age_45_54_specific, Count(*)
      FROM AgeCount
      GROUP BY Presence_of_adults_age_45_54_specific) as Normalized
  PIVOT
    (SUM(Cnt)
    FOR Code IN ([1],[2],[3])
     ) as Pvt
  ORDER BY AgeRange

HTH,
-Paul




-----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