question

Tom Staab avatar image
Tom Staab asked

Dynamic Pivot with Multiple Aggregates

After hours of internet searching, I have found several examples of dynamic pivot techniques, but I am having trouble making any of them work for my situation.

Given this data:

SELECT 1 AS month_num, 'Steve' AS customer, 50.00 AS purchase_amt, 10.00 AS discount_amt
UNION
SELECT 1, 'Mary', 45.00, 9.00
UNION
SELECT 1, 'Mary', 15.00, 0.00
UNION
SELECT 2, 'Mark', 40.00, 5.00

I would like this output:

                 January           February
Customer    Purchase Discount  Purchase Discount
Mark          0.00     0.00      40.00    5.00
Mary         60.00     9.00       0.00    0.00
Steve        50.00    10.00       0.00    0.00

I don't care what the 0 values display (0, blank, N/A, etc.), and I realize I may need to generate the final output in ASP.NET (which is how it will be displayed to the customer). My question here is how to get multiple aggregates within the same pivot column (or fake it). Thanks.

sql-server-2005pivotdynamic
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

I haven't had the chance to work with this yet - but it seems to me you're starting with a pivoted data set - so if you unpivot that set first, so you end up with the columns:

CustomerName
DateAndFunction
Value

Then you could re-pivot the set back to what you wanted...

The values in the DateAndFunction column would want to be something like 'January_Sales', 'February_Sales' etc...

edit -> I have now had the time to look at it properly:

Note that the columns don't come out in a pretty order, but, given the nature of PIVOTs (i.e. unknown columns based on data) then I think that's better handled by the client anyway.

-- Create a work table to hold the unpivoted data
CREATE TABLE #unpivotedWorkTable (
  MonthAction VARCHAR (MAX)  ,
  customer    VARCHAR (MAX)  ,
  Amount      DECIMAL (12, 2)
);

-- insert the data into the unpivoted table by using unpivot to get the set we need
INSERT INTO #unpivotedWorkTable ([MonthAction], [customer], [Amount])
SELECT DATENAME(m, DATEADD(m, [month_num] - 1, '20000101')) + '_' + [action] AS MonthAction,
       [customer],
       Amount
FROM   (SELECT 1 AS month_num, 'Steve' AS customer, 50.00 AS purchase_amt, 10.00 AS discount_amt
        UNION ALL
        SELECT 1, 'Mary', 45.00, 9.00
        UNION ALL
        SELECT 1, 'Mary', 15.00, 0.00
        UNION ALL
        SELECT 2, 'Mark', 40.00, 5.00
        UNION ALL
        SELECT 4, 'Jim', 40.00, 5.00
        UNION ALL
        SELECT 5, 'Jim', 40.00, 5.00
        UNION ALL
        SELECT 3, 'Tom', 40.00, 5.00) AS core_data 
UNPIVOT (Amount FOR [action] IN ([purchase_amt], [discount_amt])) AS unpvt;

-- now get the list of pivot columns that we will need from the unpivoted data
DECLARE @pivot_columns AS VARCHAR (MAX);
SELECT   @pivot_columns = ISNULL(@pivot_columns + ', ', '') + '[' + [MonthAction] + ']'
FROM     #unpivotedWorkTable
GROUP BY [MonthAction]

-- now use dynamic SQL to pivot the data back to how it's needed
DECLARE @sql AS VARCHAR (MAX);
SET @sql = 'SELECT * FROM #unpivotedWorkTable
PIVOT (SUM(Amount) FOR MonthAction IN (' + @pivot_columns + ')) AS pvt';
EXECUTE (@sql);

-- now drop the unpivoted work table
DROP TABLE #unpivotedWorkTable;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

No chance of doing this at the client? Where is the data being used, if its SSRS or something similar then it will be infinitely easier...

4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think I might end up doing it at the client ( ASP.NET) since there doesn't seem to be any easy way to do it in SQL. When I originally asked the question I thought maybe I was missing something obvious but perhaps not.
0 Likes 0 ·
There are far better TSQL writers than me on this forum but looking at your desired output I had a gut feeling that TSQL wont do it easily as you have columns Purchase and Discount within columns for each month and that just looks tricky! At the end of the day it could well be possible but do you want to tie your server in knots and make code maintenance harder when you could put this in a asp.net object and have it easier?
0 Likes 0 ·
Heh, you don't have to worry about it too much now :)
0 Likes 0 ·
I was just told "absolutely not" regarding pivoting on the client. I need the correct output generated in SQL Server to just dump into a table. Fortunately, Matt got me about 95% of the way there.
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered

Tom, thanks for adding (or fake it) to your requirements :)

;WITH cte AS (
SELECT 1 AS month_num, 'Steve' AS customer, 50.00 AS purchase_amt, 10.00 AS discount_amt
UNION
SELECT 1, 'Mary', 45.00, 9.00
UNION
SELECT 1, 'Mary', 15.00, 0.00
UNION
SELECT 2, 'Mark', 40.00, 5.00
)
,cte2 AS (
SELECT Customer,STUFF(REPLICATE(' ',300),month_Num * 20,20, 
                CAST(Purchase_Amt AS char(10)) +CAST(Discount_Amt AS char(10))) [d]
FROM cte
)
SELECT Customer,SUM(CAST('0'+SUBSTRING(d,20,10) AS decimal(10,2)))[Purchase],SUM(CAST('0'+SUBSTRING(d,30,10) AS decimal(10,2)))[Discount]
               ,SUM(CAST('0'+SUBSTRING(d,40,10) AS decimal(10,2)))[Purchase],SUM(CAST('0'+SUBSTRING(d,50,10) AS decimal(10,2)))[Discount]
FROM cte2
GROUP BY Customer
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I started it, so I figured I'd finish it. Just another approach without using pivot
1 Like 1 ·
Matt already solved my problem, but I appreciate the effort (+1).
0 Likes 0 ·
klbaiju avatar image
klbaiju answered
dear tom staab, can you send the final code
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.