How do I aggregate on more than one column within a PIVOT

Hi,

I wonder if you can help?

-- The following works fine
WITH PivotData AS
(
       SELECT
              [CustomerID], -- grouping column
              [ShipMethodID], -- spreading column
              freight -- aggregation column
       FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
)
SELECT [CustomerID], [1], [2], [3]
FROM PivotData
       PIVOT (SUM(freight) FOR [ShipMethodID] IN ([1],[2],[3])) AS P;


-- However I need to aggregate on two or more columns not just one
WITH PivotData AS
(
       SELECT
              [CustomerID], -- grouping column
              [ShipMethodID], -- spreading column
              freight, [CurrencyRateID] -- aggregation columns
       FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
)
SELECT [CustomerID], [1], [2], [3]
FROM PivotData
       PIVOT (SUM(freight), COUNT(CurrencyRateID)) FOR [ShipMethodID] IN ([1],[2],[3])) AS P;

-- The above returns a syntax error; - Msg 102,  level 15, state 1, line 12 Incorrect syntax near ','.

So if anyone can provide me with the syntax on how to aggregate on more than one column within a pivot that would be great.

Kind Regards,

Kieran.

---

Kieran Patrick Wood http://ift.tt/1m26DwN http://ift.tt/1m26EAK http://ift.tt/1m26DwP