Display Zero Instead of Null in Dynamic Pivot in SQL Server

Here, we will learn about the Dynamic pivot and removing the null value in the SQL Server.

Let's now learn about the Dynamic pivot and about removing the null value in the 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 the normal pivot, I will use the concept of the Dynamic pivot
--First declare the productlist variable to store all the productname

Declare @Productlist1 varchar(max), @Productlist2 varchar(max);

--Select the distinct product from the ProductSale and use this query to get all the product, also apply this login in your @productlist to store all the productname

set @Productlist1 =SUBSTRING(( select distinct ',['+Product+']' from ProductSale for xml path('')),2,8000)
set @Productlist2 =SUBSTRING(( select distinct ',IsNull(['+Product+'],0) as ['+Product+']' from ProductSale for xml path('')),2,8000)Declare @query varchar(max);
set @query ='

Select Empid, '+@Productlist2+' from
(
select Empid , Product , Quantity from ProductSale
) as sourcecode
pivot
(
sum(Quantity) for Product in ('+@Productlist1+')
) Dynamicpivot '
execute (@query)
(Note: carefully check @Productlist1 and @Productlist2 i am putting where)

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

--Select the following code and Run this query

Declare @Productlist1 varchar(max), @Productlist2 varchar(max),@query varchar(max);
set @Productlist1 =SUBSTRING(( select distinct ',['+Product+']' from ProductSale for xml path('')),2,8000)
set @Productlist2 =SUBSTRING(( select distinct ',IsNull(['+Product+'],0) as ['+Product+']' from ProductSale for xml path('')),2,8000)
set @query ='

Select Empid, '+@Productlist2+' from
(
select Empid , Product , Quantity from ProductSale
) as sourcecode
pivot
(
sum(Quantity) for Product in ('+@Productlist1+')
) Dynamicpivot '

execute (@query)