need help in sql query(multiple pivots)

Question

Hi Guys

I am stuck in a query. Basically my query works perfectly if i have two sql statement with individual pivots and the desired output is correct. However when i am trying to combine both the query and make one sql statement with two pivots i see duplicates. Not sure where i am going wrong. Any help would be appreciated.

select a.product_code as SKU,a.PRODUCT_DESCRIPTION as Product_Description
into #vendor_product
from dbo.dim_product a
inner join dbo.DIM_VENDOR b
on a.VENDOR_CODE = b.VENDOR_CODE
where b.VENDOR_COMPANY_NAME like 'Dol%'

;with CompanySalesUnitSold as
(
select case when len(PRODUCT_CODE) < 6 then right(('000000'+PRODUCT_CODE),6) else PRODUCT_CODE End as [Product Code],
vr.PRODUCT_DESCRIPTION ,
p.SALE_TOT_QTY as UnitsSold,
p.SALE_NET_VAL
,case when cast(d.DATE_FLD as date)=cast(getdate()-6 as date) then 'QtySoldLast6Days'
when cast(d.date_fld as date)=cast(getdate()-5 as date) then 'QtySoldLast5Days'
when cast(d.date_fld as date)=cast(getdate()-4 as date) then 'QtySoldLast4Days'
when cast(d.date_fld as date)=cast(getdate()-3 as date) then 'QtySoldLast3Days'
when cast(d.date_fld as date)=cast(getdate()-2 as date) then 'QtySoldLast2Days'
when cast(d.date_fld as date)=cast(getdate()-1 as date) then 'QtySoldLast1Days'
end as UnitSoldBD
,case when cast(d.DATE_FLD as date)=cast(getdate()-6 as date) then 'SalesinLast6Days'
when cast(d.date_fld as date)=cast(getdate()-5 as date) then 'SalesinLast5Days'
when cast(d.date_fld as date)=cast(getdate()-4 as date) then 'SalesinLast4Days'
when cast(d.date_fld as date)=cast(getdate()-3 as date) then 'SalesinLast3Days'
when cast(d.date_fld as date)=cast(getdate()-2 as date) then 'SalesinLast2Days'
when cast(d.date_fld as date)=cast(getdate()-1 as date) then 'SalesinLast1Days'
end as SalesBD

from 
AGG_DLY_STR_PROD p 
join DIM_DATE d  on d.DATE_KEY = p.DATE_KEY
join DIM_STORE s  on s.STORE_CODE = p.STORE_CODE
join #vendor_product vr  on vr.SKU = p.PRODUCT_CODE
where p.SALE_TOT_QTY<>0 and s.STORE_CODE=100

)
select [Product Code],Product_Description,
cast(isnull([QtySoldLast6Days],0) as int) as /[QuantitySoldInLast6Days],
cast(isnull([QtySoldLast5Days],0)as int)as [QuantitySoldInLast5Days],
cast(isnull([QtySoldLast4Days],0)as int)as [QuantitySoldInLast4Days],
cast(isnull([QtySoldLast3Days],0)as int)As [QuantitySoldInLast3Days],
cast(isnull([QtySoldLast2Days],0)as int)As [QuantitySoldInLast2Days],
cast(isnull([QtySoldLast1Days],0)as int)As [QuantitySoldInLast1Days],
cast(isnull([SalesinLast6Days],0) as int) as [SalesinLast6Days],
cast(isnull([SalesinLast5Days],0)as int)as [SalesinLast5Days],
cast(isnull([SalesinLast4Days],0)as int)as [SalesinLast4Days],
cast(isnull([SalesinLast3Days],0)as int)As [SalesinLast3Days],
cast(isnull([SalesinLast2Days],0)as int)As [SalesinLast2Days],
cast(isnull([SalesinLast1Days],0)as int)As [SalesinLast1Days]

into #UnitSold
from
(
select [Product Code],Product_Description,
sum(UnitsSold) as UnitsSold,
sum(SALE_NET_VAL) as TotalSales,
UnitSoldBD,SalesBD
from
CompanySalesUnitSold with(nolock)
group by [Product Code],Product_Description,UnitSoldBD,SalesBD
)as pt
pivot(max(UnitsSold)
for UnitSoldBD in([QtySoldLast6Days],[QtySoldLast5Days],[QtySoldLast4Days],[QtySoldLast3Days],[QtySoldLast2Days],[QtySoldLast1Days]
))Pivottable
pivot(max(TotalSales)
for SalesBD in([SalesinLast6Days],[SalesinLast5Days],[SalesinLast4Days],[SalesinLast3Days],[SalesinLast2Days],[SalesinLast1Days]
))Pivottable2

select a.[Product Code],a.Product_Description,a.QuantitySoldInLast6Days,a.SalesinLast6Days,a.QuantitySoldInLast5Days,a.SalesinLast5Days,
a.QuantitySoldInLast4Days,a.SalesinLast4Days,a.QuantitySoldInLast3Days,a.SalesinLast3Days,a.QuantitySoldInLast2Days,a.SalesinLast2Days,a.QuantitySoldInLast1Days,
a.SalesinLast1Days

from #UnitSold a

Answer 1

Hi @Farhan Jamil
There're several methods to achieve multiple Pivots:
First, If you want to keep using the PIVOT keyword twice ,then make some changes to your original query code,like this:

;with CompanySalesUnitSold as  
(  
 ......  
)  
select [Product Code],Product_Description,  
max(cast(isnull([QtySoldLast6Days],0) as int)) as [QuantitySoldInLast6Days],  
max(cast(isnull([QtySoldLast5Days],0)as int))as [QuantitySoldInLast5Days],  
max(cast(isnull([QtySoldLast4Days],0)as int))as [QuantitySoldInLast4Days],  
max(cast(isnull([QtySoldLast3Days],0)as int))As [QuantitySoldInLast3Days],  
max(cast(isnull([QtySoldLast2Days],0)as int))As [QuantitySoldInLast2Days],  
max(cast(isnull([QtySoldLast1Days],0)as int))As [QuantitySoldInLast1Days],  
max(cast(isnull([SalesinLast6Days],0)as int)) as [SalesinLast6Days],  
max(cast(isnull([SalesinLast5Days],0)as int))as [SalesinLast5Days],  
max(cast(isnull([SalesinLast4Days],0)as int))as [SalesinLast4Days],  
max(cast(isnull([SalesinLast3Days],0)as int))As [SalesinLast3Days],  
max(cast(isnull([SalesinLast2Days],0)as int))As [SalesinLast2Days],  
max(cast(isnull([SalesinLast1Days],0)as int))As [SalesinLast1Days]  
into #UnitSold  
from (  
select [Product Code],Product_Description,sum(UnitsSold) as UnitsSold,sum(SALE_NET_VAL) as TotalSales,UnitSoldBD,SalesBD  
from CompanySalesUnitSold with(nolock)  
group by [Product Code],Product_Description,UnitSoldBD,SalesBD  
)as pt  
pivot(max(UnitsSold) for UnitSoldBD in([QtySoldLast6Days],[QtySoldLast5Days],[QtySoldLast4Days],[QtySoldLast3Days],[QtySoldLast2Days],[QtySoldLast1Days]))Pivottable  
pivot(max(TotalSales) for SalesBD in([SalesinLast6Days],[SalesinLast5Days],[SalesinLast4Days],[SalesinLast3Days],[SalesinLast2Days],[SalesinLast1Days]))Pivottable2  
group by [Product Code],Product_Description  

Second, use CASE WHEN along with Aggregate Function,like this:

;WITH CompanySalesUnitSold AS  
(......  
)  
SELECT [Product Code],Product_Description,  
       SUM(CASE WHEN UnitSoldBD='QtySoldLast6Days' THEN UnitsSold END) AS QtySoldLast6Days,  
	   SUM(CASE WHEN UnitSoldBD='QtySoldLast5Days' THEN UnitsSold END) AS QtySoldLast5Days,  
	   SUM(CASE WHEN UnitSoldBD='QtySoldLast4Days' THEN UnitsSold END) AS QtySoldLast4Days,  
	   SUM(CASE WHEN UnitSoldBD='QtySoldLast3Days' THEN UnitsSold END) AS QtySoldLast3Days,  
	   SUM(CASE WHEN UnitSoldBD='QtySoldLast2Days' THEN UnitsSold END) AS QtySoldLast2Days,  
	   SUM(CASE WHEN UnitSoldBD='QtySoldLast1Days' THEN UnitsSold END) AS QtySoldLast1Days,  
	   SUM(CASE WHEN SalesBD='SalesinLast6Days' THEN SALE_NET_VAL END) AS SalesinLast6Days,  
	   SUM(CASE WHEN SalesBD='SalesinLast5Days' THEN SALE_NET_VAL END) AS SalesinLast5Days,  
	   SUM(CASE WHEN SalesBD='SalesinLast4Days' THEN SALE_NET_VAL END) AS SalesinLast4Days,  
	   SUM(CASE WHEN SalesBD='SalesinLast3Days' THEN SALE_NET_VAL END) AS SalesinLast3Days,  
	   SUM(CASE WHEN SalesBD='SalesinLast2Days' THEN SALE_NET_VAL END) AS SalesinLast2Days,  
	   SUM(CASE WHEN SalesBD='SalesinLast1Days' THEN SALE_NET_VAL END) AS SalesinLast1Days  
FROM CompanySalesUnitSold  
GROUP BY [Product Code],Product_Description  

Third, you can unpivot first, then pivot the data into the final result that you want.Check this:

;with CompanySalesUnitSold as  
(  
 ......  
),CTE1 AS  
(  
 select [Product Code],Product_Description,sum(UnitsSold) as UnitsSold,sum(SALE_NET_VAL) as TotalSales,UnitSoldBD,SalesBD  
 from CompanySalesUnitSold with(nolock)  
 group by [Product Code],Product_Description,UnitSoldBD,SalesBD  
),CTE2 AS  
(  
SELECT [Product Code],Product_Description,C.VALUE,C.COLUMN_NAME  
FROM #test CROSS APPLY(VALUES(UnitsSold,UnitSoldBD),  
                             (TotalSales,SalesBD))C(VALUE,COLUMN_NAME)  
)  
SELECT *   
FROM CTE2 PIVOT(MAX(VALUE) FOR COLUMN_NAME IN([QtySoldLast6Days],[QtySoldLast5Days],[QtySoldLast4Days],[QtySoldLast3Days],[QtySoldLast2Days],[QtySoldLast1Days],  
                                              [SalesinLast6Days],[SalesinLast5Days],[SalesinLast4Days],[SalesinLast3Days],[SalesinLast2Days],[SalesinLast1Days]))P  

BTW,since you didn't provide sample data, I can only write the query code, I can't test whether it can be executed normally and get the result. So while asking questions, please simplify the problem and provide sample data and the result you want to achieve. For example, for this issue, you need to create a temp table which contains the sample data before pivoting.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As Naomi says, to help you with a query, we would need CREATE TABLE + INSERT statements with sample data and the desired result given the sample.

But I will give you a tip: stay away from the PIVOT operator. I did not try to read you query, because it uses exactly that operator, and I never bother to learn it.

It is a lot easier to write pivot queries with aggregates and CASE. Not the least if you want to make more advanced pivoting.

I am not going to give examples here, but rather I will point you to section in an article where I have written about it: https://www.sommarskog.se/dynamic_sql.html#pivot. That is from an article on dynamic SQL, and the section discusses dynamic pivot. But it starts off with discussing static pivot, and I think that those examples should be helpful for you.