Get Monthly 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,   DATENAME(MONTH, so .orderDate) As Month_Name, so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so

 
     

     

Now we are creating the Month columns dynamically.

select stuff((Select ','+quotename(x.Month_Name) from (select distinct DATENAME(MONTH, orderDate) As Month_Name, Month(OrderDate)  as Month_ID FROM sales.SalesOrderHeader ) as x order by x.Month_ID asc for xml path(''),type).value('.','nvarchar(max)'),1,1,'')

     

Now we are creating the dynamic pivot query.

DECLARE     @columns NVARCHAR(MAX) = ''; DECLARE     @sqlquery NVARCHAR(MAX) = ''; set          @columns= stuff((Select ','+quotename(x.Month_Name) from (select distinct DATENAME(MONTH, orderDate) As Month_Name, Month(OrderDate)  as Month_ID FROM sales.SalesOrderHeader ) as x order by x.Month_ID asc for xml path(''),type).value('.','nvarchar(max)'),1,1,'') print @columns set @sqlquery=' select sales_Year, '+@columns+' from ( Select  year(so.OrderDate) as sales_Year,   DATENAME(MONTH, orderDate) As Month_Name, so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  )  as sales  pivot  (   SUM(Total_Sales)   FOR Month_Name IN ('+@columns+')  )pvt  order by sales_year  ';   -- execute the dynamic SQL EXECUTE sp_executesql @sqlquery;

Executing this script.