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:
but when I try to use
PIVOT function with dynamic SQL it returns multiple nulls as shown below:
the 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 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?