Dynamic pivot in the sql server

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.

Read static pivot: Pivot in SQL server

https://bageshkumarbagi-msbi.blogspot.com/2020/06/pivot-in-sql-server.html

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

Read more about Quotename Function:

https://bageshkumarbagi-msbi.blogspot.com/2018/12/quotename-function-in-sql-server.html

We also need to get this result into one row like

[2011],[2012],[2013],[2104]

Read Concatenate Rows using FOR XML PATH ()

https://bageshkumarbagi-msbi.blogspot.com/2016/05/concatenate-rows-using-for-xml-path.html

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.

static pivot : https://bageshkumarbagi-msbi.blogspot.com/2020/06/pivot-in-sql-server.html

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