SQL Server PIVOT operator rotates a table-valued expression. It turns the unique values in one column into multiple columns in the output and performs aggregations on any remaining column values.
You follow these steps to make a query a pivot table:
- First, select a base dataset for pivoting.
- Second, create a temporary result by using a derived table or common table expression (CTE)
- Third, apply the
PIVOToperator.
Input Table: 
Goal
step 1. base dataset query
select CustomerKey,EnglishCountryRegionName from DimCustomer c join DimGeography g
on c.GeographyKey=g.GeographyKey
step 2. temporary result query
select * from
(select CustomerKey,EnglishCountryRegionName from DimCustomer c join DimGeography g
on c.GeographyKey=g.GeographyKey
)
d
step 3: apply pivot
select * from
(select CustomerKey,EnglishCountryRegionName from DimCustomer c join DimGeography g
on c.GeographyKey=g.GeographyKey
)
d
pivot(count(customerkey)
for englishcountryregionname in
(Australia,Canada,France,Germany,[United Kingdom],[United States])
)p
we can add other column to group the category:
select * from
(select year(BirthDate) birthyear,CustomerKey,EnglishCountryRegionName from DimCustomer c join DimGeography g
on c.GeographyKey=g.GeographyKey
)
d
pivot( count(customerkey)
for englishcountryregionname in
(Australia,Canada,France,Germany,[United Kingdom],[United States])
)p
Dynamic Pivot:
If you add a new region to the englishcountryregionname table, you need to rewrite your query, which is not ideal. To avoid doing this, you can use dynamic SQL to make the pivot table dynamic.
declare @columns nvarchar(max)='',
@sql nvarchar(max)='';
select @columns+=QUOTENAME(EnglishCountryRegionName)+','
from (select distinct EnglishCountryRegionName from DimGeography)a
set @columns=LEFT(@columns,len(@columns)-1)
set @sql='
select * from
(select year(BirthDate) birthyear,CustomerKey,EnglishCountryRegionName from DimCustomer c join DimGeography g
on c.GeographyKey=g.GeographyKey
)
d
pivot( count(customerkey)
for englishcountryregionname in
('+@columns+')
)p
';
EXECUTE sp_executesql @sql;
