SQL Server pivot on 2 (or multiple) aggregates

Question

Is it possible to pivot on multiple aggregated columns in SQL Server? I have the following order table:

[ORDERS]
| CustName | OrderedProduct | QtyOrdered | UnitCost | UnitPrice |  OrderDate
  Bob      | Canned Tuna    |          6 |       11 |        14 | 21-12-2016 13:11:00
  Steve    | Canned Salmon  |          2 |       15 |        19 | 03-11-2016 11:03:00
  Bob      | Canned Tuna    |         10 |       10 |        13 | 22-12-2016 10:43:00
  Bob      | Canned Tuna    |          4 |        9 |        10 | 13-11-2016 17:22:00

I have the following pivot query which gives me an order quantity per product per customer for months Nov/Dec:

SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016] FROM
(
    SELECT CustName, OrderedProduct, QtyOrdered,
    'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
    FROM [ORDERS]
) src
PIVOT
(
    SUM(QtyOrdered)
    FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
) pvtvol
WHERE CustName='Bob'

This gives the, expected, result

CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016
Bob      | Canned Tuna    |           16 |            4

What I want is to also pivot on margin so changing the src query to:

    SELECT CustName, OrderedProduct, QtyOrdered,
    ((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
    'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
    'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
    FROM [ORDERS]

I have tried the following modified query adding a second pivot

SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016], [MAR Dec 2016], [MAR Nov 2016] FROM
(
    SELECT CustName, OrderedProduct, QtyOrdered,
    ((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
    'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
    'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
    FROM [ORDERS]
) src
PIVOT
(
    SUM(QtyOrdered)
    FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
) pvtvol
PIVOT
(
    SUM(Margin)
    FOR YearMonthMar IN ([MAR Dec 2016], [MAR Nov 2016])
) pvtmar
WHERE CustName='Bob'

The table I would EXPECT to see is

CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016 | MAR Dec 2016 | MAR Nov 2016
Bob      | Canned Tuna    |           16 |            4 |           48 |            4

The table I actually see is

CustName | OrderedProduct | VOL Dec 2016 | VOL Nov 2016 | MAR Dec 2016 | MAR Nov 2016
Bob      | Canned Tuna    |           16 |         NULL |           48 |         NULL
Bob      | Canned Tuna    |         NULL |            4 |         NULL |            4

So, the pivot on margin does not seem to be applied, and I get a row for each order. Is what I'm trying to achieve actually possible with the method I'm trying to use, or would I be better to use a crosstab query for VOL and MAR or a mixture of pivot and crosstab (pivot for VOL, crosstab for MAR?)

Any advice greatly appreciated.

Add a `GROUP BY CustName, OrderedProduct` in your second query. — Rigerta, Jul 27 '17 at 14:25
To the **src** query? Same result when I do that and I'm also forced to include QtyOrdered in the GROUP BY — Gord, Jul 27 '17 at 14:36
No, to the final query with two pivots. I think this is what is missing. — Rigerta, Jul 27 '17 at 14:37

score 6 · Answer 1 · answered Jul 27 '17 at 14:38

6

You could simplify your query a bit

Example

Select *
 From (
        Select A.CustName 
              ,A.OrderedProduct
              ,B.*
         From  ORDERS A
         Cross Apply ( values ('VOL '+left(DateName(Month,OrderDate),3)+' '+DateName(Year,OrderDate), QtyOrdered )
                             ,('MAR '+left(DateName(Month,OrderDate),3)+' '+DateName(Year,OrderDate),((UnitPrice-UnitCost)*QtyOrdered) )
                     ) B (Item,Value)
         Where CustName='Bob'
      ) A
 Pivot (sum([Value]) For [Item] in ([VOL Dec 2016], [VOL Nov 2016],[MAR Dec 2016], [MAR Nov 2016]) ) p

Returns

answered Jul 27 '17 at 14:38

John Cappelletti

79,615
7
44
66

Beat me by some seconds :D was preparing demo ;) – Pரதீப் Jul 27 '17 at 14:40
1

Significantly faster than the multiple cte pivot approach – tone Jan 13 '21 at 06:55

Pரதீப் · Answer 2 · 2017-07-27T14:49:18.503

You need to unpivot and pivot

SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016], [MAR Dec 2016], [MAR Nov 2016] FROM
(
    SELECT CustName, OrderedProduct,YearMonth,val
    FROM [ORDERS]
    cross apply (values ('VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120),QtyOrdered),('MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120),((UnitPrice-UnitCost)*QtyOrdered))) tc
    (YearMonth,val)
    WHERE CustName='Bob'
) src
PIVOT
(
    SUM(val)
    FOR YearMonth IN ([VOL Dec 2016], [VOL Nov 2016],[MAR Dec 2016], [MAR Nov 2016])
) pvtvol

Demo

The duplicate records in your result is due to presence of QtyOrdered and Margin in pivot source query. QtyOrdered and Margin has to be individually pivoted to avoid duplicates. It has to be done like this

;with QtyOrdered as
(
SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016]FROM
(
    SELECT CustName, OrderedProduct, QtyOrdered,
    'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
    FROM [ORDERS]
    WHERE CustName='Bob'
) src
PIVOT
(
    SUM(QtyOrdered)
    FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
) pvtvol
),Margin as 
(
    select * from (
SELECT CustName, OrderedProduct, ((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
    'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
    FROM [ORDERS]
    ) a
PIVOT
(
    SUM(Margin)
    FOR YearMonthMar IN ([MAR Dec 2016], [MAR Nov 2016])
) pvtmar
WHERE CustName='Bob'    
)
select * from QtyOrdered q
join Margin m on q.CustName = m.CustName 
and q.OrderedProduct = m.OrderedProduct

Thank you for the explanation, that makes perfect sense now. — Gord, Jul 27 '17 at 15:04

score -1 · Answer 3 · edited May 20 '21 at 10:35

-1

SELECT CustName, OrderedProduct, [VOL Dec 2016], [VOL Nov 2016], [MAR Dec 2016], [MAR Nov 2016] FROM
(
    SELECT CustName, OrderedProduct, QtyOrdered,
    ((UnitPrice-UnitCost)*QtyOrdered) AS Margin,
    'VOL ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthVol
    'MAR ' + CONVERT(CHAR(4), OrderDate, 100) + CONVERT(CHAR(4), OrderDate, 120) AS YearMonthMar
    FROM [ORDERS]
) src
PIVOT
(
    SUM(QtyOrdered)  qty, SUM(Margin) margin
    FOR YearMonthVol IN ([VOL Dec 2016], [VOL Nov 2016])
) pvtvol

WHERE CustName='Bob'

edited May 20 '21 at 10:35

Dejan Dozet

948
10
26

answered May 19 '21 at 21:52

user15975757

1

you should do code formatting when posting: https://stackoverflow.com/help/formatting – Dejan Dozet May 20 '21 at 06:45
You can't aggregate multiple columns in one pivot. – Dale K Oct 07 '21 at 21:03

SQL Server pivot on 2 (or multiple) aggregates

3 Answers3

Linked