Jerry Nixon on Windows: How to use the new SQL 2005 PIVOT command in tSQL

Jerry Nixon on Windows

Friday, March 14, 2008

How to use the new SQL 2005 PIVOT command in tSQL

It's not easy - and the uses are limited. But, here it is:

/*
PIVOT
*/

SET NOCOUNT ON

CREATE TABLE [t] (Teacher varchar(25), Student varchar(25), Grade int, Gender char(1))

Insert Into [t] Select 'Ms. Smith', 'Johnny', 2, 'm';
Insert Into [t] Select 'Ms. Smith', 'Jimmy', 1, 'm';
Insert Into [t] Select 'Ms. Smith', 'Sally', 4, 'f';
Insert Into [t] Select 'Ms. Smith', 'Susan', 4, 'f';
Insert Into [t] Select 'Ms. Smith', 'Martin', 2, 'm';
Insert Into [t] Select 'Ms. Smith', 'Mary', 3, 'f';
Insert Into [t] Select 'Ms. Jones', 'Anna', 3, 'f';
Insert Into [t] Select 'Ms. Jones', 'Jenny', 3, 'f';
Insert Into [t] Select 'Ms. Jones', 'Waldo', 4, 'm';
Insert Into [t] Select 'Ms. Jones', 'Karl', 2, 'm';
Insert Into [t] Select 'Ms. Jones', 'Lucy', 4, 'f';
Insert Into [t] Select 'Ms. Smith', 'Kelly', 3, 'f';

print 'Raw data'

Select Teacher, Student, Grade, Gender from [t]

print 'Average grade by teacher'

Select
Teacher
,Avg(Convert(decimal, Grade)) As [Average Grade]
From [t]
Group By Teacher

print 'Classroom size by teacher'

Select
Teacher
,Count(Student) As [Classroom Size]
From [t]
Group By Teacher

print 'Grade distribution by teacher'

Select Teacher, [1] As [D], [2] As [C], [3] As [B], [4] As [A] From
(Select Teacher, Grade From [t]) As Data
Pivot (Count(Grade) For grade in ([1], [2], [3], [4])) As PivotTable
Order by Teacher

print 'Gender distribution'

Select Gender, [Ms. Smith] As [Ms. Smith], [Ms. Jones] As [Ms. Jones] From
(Select Teacher, Grade, Case Gender When 'f' Then 'Girls' When 'm' Then 'Boys' End As Gender From [t]) As Data
Pivot (Count(grade) For teacher in ([Ms. Smith], [Ms. Jones])) As PivotTable
Order by Gender

print 'Average gender grade '

Select Teacher, [m] As [Boys], [f] As [Girls] From
(Select Teacher, Convert(decimal, Grade) Grade, Gender From [t]) As Data
Pivot (Avg(grade) For gender in ([m], [f])) As PivotTable
Order by Teacher

print 'Grade distribution by gender'

Select Gender, [1] As [D], [2] As [C], [3] As [B], [4] As [A] From
(Select Teacher, Grade, Case Gender When 'f' Then 'Girls' When 'm' Then 'Boys' End As Gender From [t]) As Data
Pivot (Count(Teacher) For grade in ([1], [2], [3], [4])) As PivotTable
Order by Gender

GO

DROP TABLE [t]