Pivot Example

The following is an example of how to use the SQL PIVOT function.

The PIVOT function groups rows and selects out the data in specific columns. The outputted columns are aggregated in sum form, SUM, AVG, MIN, MAX, etc.

Start with a table, [PivotExample], with the following data:

RowID	GrpA	ColB	NumC
5	XXX	AA	7
1	XXX	AA	2
2	XXX	BB	3
6	XXX	BB	8
7	XXX	CC	9
8	XXX	CC	10
3	XXX	CC	4
4	XXX	DD	5
9	XXX	DD	11
10	YYY	AA	5
11	YYY	AA	6
14	YYY	AA	9
15	YYY	AA	10
16	YYY	BB	11
12	YYY	BB	7
13	YYY	CC	8

When pivoting a table, it is important to only select the columns that are of interest. The following example uses a CTE (Common Table Expression) to select the columns of interest.

;with cteA as (
   select a.GrpA, a.ColB, a.NumC
   from PivotExample a
)
select p.GrpA, p.AA, p.BB, p.CC, p.DD
from cteA a
pivot 
(
   sum(NumC)
   for ColB in (AA, BB, CC, DD)
) p

The above SQL results in the following output:

GrpA	AA	BB	CC	DD
XXX	9	11	23	16
YYY	30	18	8	NULL

If the columns selected for the pivot include all the columns, as in the following example, the output will probably not be what is desired.

select p.GrpA, p.AA, p.BB, p.CC, p.DD
from PivotExample a
pivot 
(
   sum(NumC)
   for ColB in (AA, BB, CC, DD)
) p

The above SQL results in the following output:

GrpA	AA	BB	CC	DD
XXX	2	NULL	NULL	NULL
XXX	NULL	3	NULL	NULL
XXX	NULL	NULL	4	NULL
XXX	NULL	NULL	NULL	5
XXX	7	NULL	NULL	NULL
XXX	NULL	8	NULL	NULL
XXX	NULL	NULL	9	NULL
XXX	NULL	NULL	10	NULL
XXX	NULL	NULL	NULL	11
YYY	5	NULL	NULL	NULL
YYY	6	NULL	NULL	NULL
YYY	NULL	7	NULL	NULL
YYY	NULL	NULL	8	NULL
YYY	9	NULL	NULL	NULL
YYY	10	NULL	NULL	NULL
YYY	NULL	11	NULL	NULL

Having the [RowID] column included in the select statement causes the results to be expanded vertically.

Click here for an UnPivot example.