Not logged in - Login

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:

RowIDGrpAColBNumC
5XXXAA7
1XXXAA2
2XXXBB3
6XXXBB8
7XXXCC9
8XXXCC10
3XXXCC4
4XXXDD5
9XXXDD11
10YYYAA5
11YYYAA6
14YYYAA9
15YYYAA10
16YYYBB11
12YYYBB7
13YYYCC8
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:

GrpAAABBCCDD
XXX9112316
YYY30188NULL
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:

GrpAAABBCCDD
XXX2NULLNULLNULL
XXXNULL3NULLNULL
XXXNULLNULL4NULL
XXXNULLNULLNULL5
XXX7NULLNULLNULL
XXXNULL8NULLNULL
XXXNULLNULL9NULL
XXXNULLNULL10NULL
XXXNULLNULLNULL11
YYY5NULLNULLNULL
YYY6NULLNULLNULL
YYYNULL7NULLNULL
YYYNULLNULL8NULL
YYY9NULLNULLNULL
YYY10NULLNULLNULL
YYYNULL11NULLNULL
Having the [RowID] column included in the select statement causes the results to be expanded vertically.

Click here for an UnPivot example.