SQL PIVOT

The PIVOT statement is used for changing rows into columns in a SQL Query (Crosstab). The PIVOT Statement is generally written in this form:



SELECT columns
FROM table
PIVOT
(
Aggregate Function(Measure Column)
FOR Pivot Column IN ([Pivot Column Values])
)
AS Alias
Note: You must use brackets around each of the Pivot Column Values


Example

declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)

insert into @sales values(2001, 'Q1', 70)
insert into @sales values(2001, 'Q1', 150)
insert into @sales values(2002, 'Q1', 20)
insert into @sales values(2001, 'Q2', 15)
insert into @sales values(2002, 'Q2', 25)
insert into @sales values(2001, 'Q3', 50)
insert into @sales values(2002, 'Q3', 20)
insert into @sales values(2001, 'Q4', 90)
insert into @sales values(2001, 'Q4', 80)
insert into @sales values(2002, 'Q4', 35)

select * from @sales
pivot
(
sum(Amount)
for Quarter
in (Q1, Q2, Q3, Q4)
) as p



Year Q1 Q2 Q3 Q4
----------- ---------------------- ---------------------- ---------------------- ----------------------
2001 220 15 50 170
2002 20 25 20 35


(2 row(s) affected)