In the previous post we saw who to create a dynamic and static
pivot for one column. In this post, we will see who to create a dynamic pivot on
multiple columns.
For this demo, we are taking
Database: AdventureWorks2017
Table: sales.SalesOrderHeader
Our requirement: we need to create a pivot which shows the
value of the Subtotal, TaxAmt, Freight, TotalDue year wise for the sales
country. In the previous post, we see an example for subtotal only. But this demo we
will how to create pivot like below.
Let’s prepare a dynamic SQL script for this.
First of all we need to create the dynamic column for the
Subtotal, TaxAmt, Freight, and TotalDue.
|
DECLARE @Sales_columns NVARCHAR(MAX) = '';
DECLARE @TaxAmt_columns NVARCHAR(MAX) = '';
DECLARE @Freight_columns NVARCHAR(MAX) = '';
DECLARE @TotalDue_columns NVARCHAR(MAX) = '';
DECLARE @sqlquery NVARCHAR(MAX) = '';
set @Sales_columns=
(SELECT distinct ',' +
QUOTENAME('Sales_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Sales_columns = Right(@Sales_columns, LEN(@Sales_columns) - 1);
set @TaxAmt_columns=
(SELECT distinct ',' +
QUOTENAME('TaxAmt_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @TaxAmt_columns = Right(@TaxAmt_columns, LEN(@TaxAmt_columns) - 1);
set @Freight_columns=
(SELECT distinct ',' +
QUOTENAME('Freight_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Freight_columns = Right(@Freight_columns, LEN(@Freight_columns) - 1);
set @TotalDue_columns=
(SELECT distinct ',' +
QUOTENAME('TotalDue_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @TotalDue_columns = Right(@TotalDue_columns, LEN(@TotalDue_columns) - 1);
print @Sales_columns
print @TaxAmt_columns
print @Freight_columns
print @TotalDue_columns
|
We will get the below columns
|
[Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014]
[TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014]
[Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014]
[TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014]
|
Here we know the list of columns. Now we need to create
pivot.
|
DECLARE @Sales_columns NVARCHAR(MAX) = '';
DECLARE @TaxAmt_columns NVARCHAR(MAX) = '';
DECLARE @Freight_columns NVARCHAR(MAX) = '';
DECLARE @TotalDue_columns NVARCHAR(MAX) = '';
DECLARE @sqlquery NVARCHAR(MAX) = '';
DECLARE @col NVARCHAR(MAX)='';
DECLARE @empty_col NVARCHAR(MAX)='';
DECLARE @Empty_Sales_columns NVARCHAR(MAX)='';
DECLARE @Empty_TaxAmt_columns NVARCHAR(MAX) = '';
DECLARE @Empty_Freight_columns NVARCHAR(MAX) = '';
DECLARE @Empty_TotalDue_columns NVARCHAR(MAX) = '';
set @Sales_columns=
(SELECT distinct ',' +
QUOTENAME('Sales_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Sales_columns = Right(@Sales_columns, LEN(@Sales_columns) - 1);
set @TaxAmt_columns=
(SELECT distinct ',' +
QUOTENAME('TaxAmt_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @TaxAmt_columns = Right(@TaxAmt_columns, LEN(@TaxAmt_columns) - 1);
set @Freight_columns=
(SELECT distinct ',' +
QUOTENAME('Freight_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Freight_columns = Right(@Freight_columns, LEN(@Freight_columns) - 1);
set @TotalDue_columns=
(SELECT distinct ',' +
QUOTENAME('TotalDue_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @TotalDue_columns = Right(@TotalDue_columns, LEN(@TotalDue_columns) - 1);
set @col=@Sales_columns+','+@TaxAmt_columns+','+@Freight_columns+','+@TotalDue_columns
set @col=replace(replace(@col,'[','SUM(ISNULL(['),']','],0))')
set @Empty_Sales_columns=
(SELECT distinct ',' +'null as '+
QUOTENAME('Sales_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Empty_Sales_columns = Right(@Empty_Sales_columns, LEN(@Empty_Sales_columns) - 1);
set @Empty_TaxAmt_columns=
(SELECT distinct ',' +'null as '+
QUOTENAME('TaxAmt_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Empty_TaxAmt_columns = Right(@Empty_TaxAmt_columns, LEN(@Empty_TaxAmt_columns) - 1);
set @Empty_Freight_columns=
(SELECT distinct ',' +'null as '+
QUOTENAME('Freight_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Empty_Freight_columns = Right(@Empty_Freight_columns, LEN(@Empty_Freight_columns) - 1);
set @Empty_TotalDue_columns=
(SELECT distinct ',' +'null as '+
QUOTENAME('TotalDue_'+''+cast(year(orderdate) as varchar(4)) )
FROM sales.SalesOrderHeader s
order by 1 FOR XML PATH(''))
SET @Empty_TotalDue_columns = Right(@Empty_TotalDue_columns, LEN(@Empty_TotalDue_columns) - 1);
set @sqlquery='
Select
Name,
CountryRegionCode,
[Group],
'+@Sales_columns+','+@TaxAmt_columns+','+@Freight_columns+','+@TotalDue_columns+'
from
(
select
null as Name,
null as CountryRegionCode,
null as [Group],
'+@Empty_Sales_columns+','+@Empty_TaxAmt_columns+','+@Empty_Freight_columns+','+@Empty_TotalDue_columns+'
)x
where 1=2
union all
select Name,
CountryRegionCode,[Group],'+@col+' from (
select
Name,
CountryRegionCode,[Group],
'+@Sales_columns+','+@TaxAmt_columns+','+@Freight_columns+','+@TotalDue_columns+' from
(
Select
''Sales_''+cast(year(orderdate) as varchar(4)) as sales_Year
,''TaxAmt_''+cast(year(orderdate) as varchar(4)) as Tax_year
,''Freight_''+cast(year(orderdate) as varchar(4)) as Freight_year
,''TotalDue_''+cast(year(orderdate) as varchar(4)) as
TotalDue_Year
,st.Name,st.CountryRegionCode,st.[Group]
,isnull(so.SubTotal,0) as Total_Sales
,isnull(so.TaxAmt,0) as Total_TaxAmt
,isnull(so.Freight,0) as Total_Freight
,isnull(so.TotalDue,0) as total_TotalDue
FROM sales.SalesOrderHeader
so
join Sales.SalesTerritory
st on st.TerritoryID=so.TerritoryID
) as sales
pivot
(
SUM(Total_Sales)
FOR Sales_Year IN ('+@Sales_columns+')
)pvt_sales
pivot
(
SUM(Total_TaxAmt)
FOR Tax_year IN ('+@TaxAmt_columns+')
)pvt_Tax_year
pivot
(
SUM(Total_Freight)
FOR Freight_year IN ('+@Freight_columns+')
)pvt_Freight_year
pivot
(
SUM(total_TotalDue)
FOR TotalDue_Year IN ('+@TotalDue_columns+')
)pvt_TotalDue_Year)x
group by Name,
CountryRegionCode,[Group]
order by name;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sqlquery;
|
It create below script
|
Select
Name,
CountryRegionCode,
[Group],
[Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014],[TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014],[Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014],[TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014]
from
(
select
null as Name,
null as CountryRegionCode,
null as [Group],
null as [Sales_2011],null as [Sales_2012],null as [Sales_2013],null as [Sales_2014],null as [TaxAmt_2011],null as [TaxAmt_2012],null as [TaxAmt_2013],null as [TaxAmt_2014],null as [Freight_2011],null as [Freight_2012],null as [Freight_2013],null as [Freight_2014],null as [TotalDue_2011],null as [TotalDue_2012],null as [TotalDue_2013],null as [TotalDue_2014]
)x
where 1=2
union all
select Name,
CountryRegionCode,[Group],SUM(ISNULL([Sales_2011],0)),SUM(ISNULL([Sales_2012],0)),SUM(ISNULL([Sales_2013],0)),SUM(ISNULL([Sales_2014],0)),SUM(ISNULL([TaxAmt_2011],0)),SUM(ISNULL([TaxAmt_2012],0)),SUM(ISNULL([TaxAmt_2013],0)),SUM(ISNULL([TaxAmt_2014],0)),SUM(ISNULL([Freight_2011],0)),SUM(ISNULL([Freight_2012],0)),SUM(ISNULL([Freight_2013],0)),SUM(ISNULL([Freight_2014],0)),SUM(ISNULL([TotalDue_2011],0)),SUM(ISNULL([TotalDue_2012],0)),SUM(ISNULL([TotalDue_2013],0)),SUM(ISNULL([TotalDue_2014],0)) from (
select
Name,
CountryRegionCode,[Group],
[Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014],[TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014],[Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014],[TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014] from
(
Select
'Sales_'+cast(year(orderdate) as varchar(4)) as sales_Year
,'TaxAmt_'+cast(year(orderdate) as varchar(4)) as Tax_year
,'Freight_'+cast(year(orderdate) as varchar(4)) as Freight_year
,'TotalDue_'+cast(year(orderdate) as varchar(4)) as TotalDue_Year
,st.Name,st.CountryRegionCode,st.[Group]
,isnull(so.SubTotal,0) as Total_Sales
,isnull(so.TaxAmt,0) as Total_TaxAmt
,isnull(so.Freight,0) as Total_Freight
,isnull(so.TotalDue,0) as total_TotalDue
FROM sales.SalesOrderHeader so
join Sales.SalesTerritory st on st.TerritoryID=so.TerritoryID
) as sales
pivot
(
SUM(Total_Sales)
FOR Sales_Year IN ([Sales_2011],[Sales_2012],[Sales_2013],[Sales_2014])
)pvt_sales
pivot
(
SUM(Total_TaxAmt)
FOR Tax_year IN ([TaxAmt_2011],[TaxAmt_2012],[TaxAmt_2013],[TaxAmt_2014])
)pvt_Tax_year
pivot
(
SUM(Total_Freight)
FOR Freight_year IN ([Freight_2011],[Freight_2012],[Freight_2013],[Freight_2014])
)pvt_Freight_year
pivot
(
SUM(total_TotalDue)
FOR TotalDue_Year IN ([TotalDue_2011],[TotalDue_2012],[TotalDue_2013],[TotalDue_2014])
)pvt_TotalDue_Year)x
group by Name,
CountryRegionCode,[Group]
order by name;
|
Running the above dynamic sql script.
Get the required result.
