Sql-server – Pivoting 4 by 44 result set

ctepivotsql serversql-server-2012unpivot

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?

Best Answer

  WITH Sales_CTE ([50001 Sales], [50002 Sales], [50001 margin],[50002 margin],[50001 profit],[50002 profit],[50001 cost],[50002 cost])  
AS  
(    
select * from 
    (
    select rtrim(cast(csstor as char))+' Sales' as s
            ,rtrim(cast(csstor as char))+' margin' as m
            ,rtrim(cast(csstor as char))+' profit' as p
            ,rtrim(cast(csstor as char))+' cost' as c
            ,[TOTAL_PRICE]
            ,[Margin] 
            ,[TOTAL_Profit]     
            ,[Total_COST]
    from sales
    
    ) as result_1
pivot (sum(total_price)     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)
SELECT
MAX([50001 Sales]) as [50001 Sales]
, MAX([50002 Sales]) AS [50002 Sales]
, MAX([50001 margin]) AS [50001 margin]
,MAX([50002 margin]) AS [50002 margin]
,MAX([50001 profit]) AS [50002 margin]
,MAX([50002 profit]) AS [50002 profit]
,MAX([50001 cost]) AS [50001 cost]
,MAX([50002 cost]) AS [50002 cost]
FROM Sales_CTE

Result is

    50001 Sales     50002 Sales 50001 margin    50002 margin    50002 margin    50002 profit    50001 cost  50002 cost
1   39498,300       31647,150   12,562300       11,159300      4961,932         3531,624        34536,3675  28115,5264

Related Solutions

SQL Server – Using Column Names in Separate Table

What you're looking for is Dynamic SQL Pivots. A PIVOT will turn row values into columns based on an aggregate, however you need to define the column names as part of the PIVOT, luckily we can do that on the fly with dynamic SQL. The following should generate the result set you want for a particular company (replace rowValues with your table name):

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()] 
                        FROM dbo.Headers H
                        WHERE H.company = @Company  
                        ORDER BY H.fieldNumber
                        FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT company,jobNumber,'+@Columns+'
FROM
(
    SELECT h.company,RV.jobNumber,RV.information,h.description
    FROM Headers h
            INNER JOIN dbo.RowValues RV
                ON RV.fieldNumber = h.fieldNumber
                   AND RV.company = h.company
    WHERE h.company = '+@Company+'  
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY jobNumber ASC'

EXEC sp_executesql @sql;

Unfortunately due to the possibility that different companies will have different columns there is no reliable way to combine all the the possible sets the above query could generate. Depending on how you want to use this query the easy option is to loop through all the companies and call an SP that contains the above, allowing you to output each company as a separate SELECT. Or you can do something with your SSIS package to output each one into a file.

If you want to insert the data into a table matching the calculated schema you can do a SELECT INSERT and a bit more dynamic SQL to get the data where it needs to go.

Sql-server – Dynamic SQL UNPIVOT and putting results into existing table

Take away the text INSERT INTO dbo.WEEKLY_SALES... and everything after it on that line. Then change SELECT src.AREAS to:

SELECT INTO dbo.WEEKLY_SALES 
src.AREAS, src.COMPANY ...etc

Also, before your SET @query statement, add:

DROP TABLE dbo.WEEKLY_SALES

That way, when it runs, it will drop yesterday's values (the entire table definition as well). Then the rest of the statement will create and load the table on the fly.

Honestly, if you're going to truncate each time anyways, you might want to consider creating the table dynamically using Select into. Basically just drop the table first, then add the into [table name] after the select keyword. In terms of straight easy coding, that's as simple as it gets. It won't give you the best performance on reading the table though because there won't be any indexes. That's really the only downside.

Best Answer

Related Solutions

SQL Server – Using Column Names in Separate Table

Sql-server – Dynamic SQL UNPIVOT and putting results into existing table

Related Question