x

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.

more ▼

asked Jan 26, 2010 at 01:57 PM in Default

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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;
more ▼

answered Jan 26, 2010 at 05:44 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

I had written a slick solution to this problem using MERGE but alas it wouldn't compile--row would be updated more than once. Too bad since I haven't found a great use for MERGE. As an aside, the usage spec in BOL for the merge match shows a quirky update as valid SET syntax but it is not allowed
Jan 27, 2010 at 09:20 AM Scot Hauder
Thank you so much for the effort on this, Matt. Sometimes it's hard to get the brain going a certain direction. Even after your comment about unpivoting, I didn't understand where you were headed. Now that I see your solution, it seems obvious. Thanks again.
Jan 28, 2010 at 01:52 PM Tom Staab
No worries mate - pivoting is a total nightmare on the best of days for me! :)
Jan 28, 2010 at 01:59 PM Matt Whitfield ♦♦
This proved to be a much bigger headache than I originally anticipated because I had to do everything in SQL, but it's finally finished. The good thing is that (at least according to the execution plan) it's 75% faster than the original non-pivoted query written by the user who requested this. The speed improvement is primarily due to my use of interim table variables earlier in the procedure instead of repeated queries against the source data. Thanks again for the help.
Jan 29, 2010 at 03:47 AM Tom Staab
No worries - when you say you had to do everything in SQL - did you have to do it all in SQL or was there the possibility to do it using the CLR? Because the final bit might have been a bit easier that way (just for future reference!).
Jan 29, 2010 at 05:32 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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...

more ▼

answered Jan 27, 2010 at 05:14 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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.
Jan 27, 2010 at 05:59 PM Tom Staab
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?
Jan 27, 2010 at 07:42 PM Fatherjack ♦♦
Heh, you don't have to worry about it too much now :)
Jan 28, 2010 at 10:33 AM Matt Whitfield ♦♦
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.
Jan 28, 2010 at 02:00 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Jan 29, 2010 at 03:10 AM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

Matt already solved my problem, but I appreciate the effort (+1).
Jan 29, 2010 at 03:52 AM Tom Staab
I started it, so I figured I'd finish it. Just another approach without using pivot
Jan 30, 2010 at 01:23 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
dear tom staab, can you send the final code
more ▼

answered Apr 30 at 06:33 AM

klbaiju gravatar image

klbaiju
0 1 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x82
x51

asked: Jan 26, 2010 at 01:57 PM

Seen: 7434 times

Last Updated: Apr 30 at 06:33 AM