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
but when I try to use 
table 
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
but when I try to use PIVOT function with dynamic SQL it returns multiple nulls as shown below:
Query:Query2
table ##tbl1 includes the data set that I want to pivot:
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 PIVOT() without having to write each one of them individually, but for the sake of troubleshooting I just showed you how the query would look like.
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 an easier way than this?
Asked by Poseidon
(162 rep)
Jan 9, 2021, 09:30 AM
Last activity: Jun 24, 2022, 05:02 PM
Last activity: Jun 24, 2022, 05:02 PM