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.

Answer 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'

Answer 2

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 

Answer 3

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