Pivot in SQL server is used when we want to transform data
from row-level to the column level.
Suppose we have data in the row and we want our final output
in the column-wise in this case we are using the pivot.
Basically, we are using the pivot for the reporting.
Dynamic pivot is very important in the report. Preview post we
learn how to create a static pivot.
select
Name,
CountryRegionCode,
[Group],
[2011],[2012],[2013],[2014] from
(
Select
year(so.OrderDate) as sales_Year
,st.Name
,st.CountryRegionCode
,st.[Group]
,so.SubTotal as Total_Sales
FROM sales.SalesOrderHeader so
join Sales.SalesTerritory st
on st.TerritoryID=so.TerritoryID
) as sales
pivot
(
SUM(Total_Sales)
FOR Sales_Year IN ([2011],[2012],[2013],[2014])
)pvt
order by name
|
The highlighted column name is static, Think if we will get the
data of 2015 onwards again we need to make the changes in this script. A better way to create a dynamic pivot.
Let’s see how to create a dynamic pivot.
For the pivot first of all we need to get the list of the
columns.
SELECT distinct year(orderdate) as sales_year
FROM sales.SalesOrderHeader s
order by 1
|
We need to add [] on this value.
To add the [] we are using Quotename function
We also need to get this result into one row like
[2011],[2012],[2013],[2104]
Now see the dynamic list of the columns
DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sqlquery NVARCHAR(MAX) = '';
set @columns=
(SELECT distinct ',' +
QUOTENAME(year(orderdate) )
FROM sales.SalesOrderHeader s
order by 1
FOR XML PATH(''))
SET @columns = Right(@columns, LEN(@columns) - 1);
PRINT @columns;
|
Get the expected result.
Now we need to create the dynamic query with dynamic
columns.
DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sqlquery NVARCHAR(MAX) = '';
set @columns=
(SELECT distinct ',' +
QUOTENAME(year(orderdate) )
FROM sales.SalesOrderHeader s
order by 1
FOR XML PATH(''))
SET @columns = Right(@columns, LEN(@columns) - 1);
set @sqlquery='
select
Name,
CountryRegionCode,[Group],
'+@columns+' from
(
Select
year(so.OrderDate) as
sales_Year,st.Name,st.CountryRegionCode,st.[Group]
,so.SubTotal as Total_Sales
FROM sales.SalesOrderHeader
so
join Sales.SalesTerritory
st on st.TerritoryID=so.TerritoryID
) as sales
pivot
(
SUM(Total_Sales)
FOR Sales_Year IN ('+@columns+')
)pvt
order by name;';
print @sqlquery
|
Let’s see the generated sql script
select
Name,
CountryRegionCode,[Group],
[2011],[2012],[2013],[2014] from
(
Select
year(so.OrderDate) as
sales_Year,st.Name,st.CountryRegionCode,st.[Group]
,so.SubTotal as Total_Sales
FROM
sales.SalesOrderHeader so
join Sales.SalesTerritory
st on st.TerritoryID=so.TerritoryID
) as sales
pivot
(
SUM(Total_Sales)
FOR Sales_Year IN
([2011],[2012],[2013],[2014])
)pvt
order by name;
|
This is the same as we have created in the previous post.
To execute the dynamic
query we are using the
EXECUTE sp_executesql
|
Below is the final
sql script
DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sqlquery NVARCHAR(MAX) = '';
set @columns=
(SELECT distinct ',' +
QUOTENAME(year(orderdate) )
FROM sales.SalesOrderHeader s
order by 1
FOR XML PATH(''))
SET @columns = Right(@columns, LEN(@columns) - 1);
set @sqlquery='
select
Name,
CountryRegionCode,[Group],
'+@columns+' from
(
Select
year(so.OrderDate) as
sales_Year,st.Name,st.CountryRegionCode,st.[Group]
,so.SubTotal as Total_Sales
FROM sales.SalesOrderHeader
so
join Sales.SalesTerritory st
on st.TerritoryID=so.TerritoryID
) as sales
pivot
(
SUM(Total_Sales)
FOR Sales_Year IN ('+@columns+')
)pvt
order by name;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sqlquery;
|
Now executing this script
