Dynamic pivot in sql server on multiple columns

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.