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

avatar image

Tom Staab ♦
14.5k 7 14 18

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

Fatherjack ♦♦
43.7k 79 97 117

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

avatar image

Scot Hauder
6.4k 13 16 22

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, 2014 at 06:33 AM

avatar image

klbaiju
0 2 2 3

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

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:

x2016
x101
x55

asked: Jan 26, 2010 at 01:57 PM

Seen: 9154 times

Last Updated: Apr 30, 2014 at 06:33 AM

Copyright 2016 Redgate Software. Privacy Policy