Get quarterly sales using dynamic pivot in sql server

In this demo, we will see how we get the quarterly sales using the dynamic pivot.

Database:  AdventureWorks2017

Table:  sales.SalesOrderHeader

We have the row data.

Select  year(so.OrderDate) as sales_Year,   CAST('Q'+CAST(DATEPART(QUARTER, so.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so

      

    

Now we are creating the Quarters columns dynamically.

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; set          @columns= (SELECT distinct ',' + QUOTENAME(CAST('Q'+CAST(DATEPART(QUARTER, s.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)))     FROM    sales.SalesOrderHeader s order by 1 FOR XML PATH('')) SET @columns = Right(@columns, LEN(@columns) - 1);

We will get the below column

[Q1],[Q2],[Q3],[Q4]

Now we are creating the dynamic pivot.

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; set          @columns= (SELECT distinct ',' + QUOTENAME(CAST('Q'+CAST(DATEPART(QUARTER, s.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)))     FROM    sales.SalesOrderHeader s order by 1 FOR XML PATH('')) SET @columns = Right(@columns, LEN(@columns) - 1); set @sqlquery=' select sales_Year, '+@columns+' from ( Select  year(so.OrderDate) as sales_Year,   CAST(''Q''+CAST(DATEPART(QUARTER, so.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  )  as sales  pivot  (   SUM(Total_Sales)   FOR Quarters IN ('+@columns+')  )pvt  order by sales_year  ';   -- execute the dynamic SQL EXECUTE sp_executesql @sqlquery;

Executing this script.

 

Here we get the quarter wise result.