Dynamic Pivot in SQL Server

Dynamic pivot in SQL Server

Dynamic Pivot is used when I don't know the limit of the given list:

create table ProductSale(Empid int,Product varchar(10), Quantity int)

select * from ProductSale

 

Insert into ProductSale values(101,'T-shirts',10) ,(101,'Paints',50) ,(120,'Paints',20) ,(120,'Socks',50),(130,'Bags',10)

Insert into ProductSale values(101,'Shirts',10) ,(101,'shoes',50) ,(120,'Cap',20) ,(120,'towel',50),(130,'socks',10)

 

------------------------------------------Note--------------------------------------

Here I do not know how many products are there in my productlist

So instead of a normal pivot, I will use the concept of the Dynamic pivot

 

--First declare the productlist variable to store all the productnames

 

Declare @Productlist varchar(max);

 

--Select the distinct product from the ProductSale. Use this query to get all the products and apply this login in your @productlist to store all the productname

set @Productlist =SUBSTRING(( select distinct ',[' +product+']' from ProductSale for xml path('')),2,8000)

 

Declare @query varchar(max);

 

set @query ='

 

Select Empid, '+@Productlist+' from

(

select Empid , Product , Quantity from ProductSale

) as sourcecode

 

pivot

(

sum(Quantity) for Product in ('+@Productlist+')

) Dynamicpivot '

 

execute (@query)

---------------------------------------------------------------------------------------------------

 

--Select the following code and Run this query

 

Declare @Productlist varchar(max),@query varchar(max);

set @Productlist =SUBSTRING(( select distinct ',[' +product+']' from ProductSale for xml path('')),2,8000)

set @query ='

 

Select Empid, '+@Productlist+' from

(

select Empid , Product , Quantity from ProductSale

) as sourcecode

 

pivot

(

sum(Quantity) for Product in ('+@Productlist+')

) Dynamicpivot '

 

execute (@query)