Adding a column from a diffent table to a pivot table on SQL

Question

I am new to sql and try to figure out a "lookup" column to a pivot table. I have managed to make a pivot with first column as the Primary key. Then I want to add a column from a different query, that is from joined tables.

I have both the pivot table and the second query working but cannot append them together. Could you help me with that

code for pivot >

 Select         Variant
                ,WH1
                ,WH2
                ,WH3 
FROM(
Select  WH
,Variant
,stock pieces 
FROM StockData
Where Date = cast(Getdate()-3 AS Date)

) Stock

Pivot( SUM(AtdPieces) FOR Warehouse  IN ([6492],[7318],[7348],[7347]) ) as Pvt
Order by Poznan desc;

code for second table

Select     Variant
           ,SUM(Sales) AS TotalSales
           ,SUM(Discount) AS TotalDisc

From SalesDatabase as SDB
Join DiscountDatabase as DDB 
On SDB.Variant=DDB. Variant)

So I want to add "total Sales" and "Total Disc" columns next to my pivot table. So for each variant I can see the Stock in WH1, stock in WH2, sotkc in WH3, Total Sale and total discount.

Thanks a lot for your helps!

Answer 1

You can try below using common table expression and join

with cte as
(
 Select         Variant
                ,WH1
                ,WH2
                ,WH3 
FROM(
Select  WH
,Variant
,stock pieces 
FROM StockData
Where Date = cast(Getdate()-3 AS Date)

) Stock

Pivot( SUM(AtdPieces) FOR Warehouse  IN ([6492],[7318],[7348],[7347]) ) as Pvt
Order by Poznan desc;
)

select cte.variant,WH1, WH2, WH3, SUM(Sales) AS TotalSales
           ,SUM(Discount) AS TotalDisc from cte join 
SalesDatabase as SDB on cte.variant=SDB.variant
Join DiscountDatabase as DDB On SDB.Variant=DDB.Variant
group by cte.variant,WH1, WH2, WH3