sql server - Pivoting 4 columns for a 44 row result

Question

I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each store to get the 4 values as columns for each store as shown below:

Query: Query1

Result 1

but when I try to use PIVOT function with dynamic SQL it returns multiple nulls as shown below:

Query:Query2 Result 2

the table ##tbl1 includes the data set that I want to pivot: ##tbl1

I used the following query: (the three dots represent the rest of the columns to be pivoted)

select * from ##tbl1 

pivot (sum(total_sales)     for s in ([50001 Sales],[50002 Sales],...)) as pv_tb 
pivot (sum(Margin)          for m in ([50001 margin],[50002 margin],...)) as pv_tb1
pivot (sum(total_profit)    for p in ([50001 profit],[50002 profit],...)) as pv_tb2
pivot (sum(total_cost)      for c in ([50001 cost],[50002 cost],...)) as pv_tb3

I also use dynamic SQL to pass the column names inside the pivot without having to type each one of them individually, but for the sake of troubleshooting I just showed you how the query would look.

I can't get around using CROSS APPLY and UNPIVOT with this 132 column result, it's very hard to maintain.

Can anyone help me find a way is there anyway easier than this?

question from:https://stackoverflow.com/questions/65640853/pivoting-4-columns-for-a-44-row-result

Answer 1

Waitting for answers