Pivot Table With Multiple Column Aggregates

CREATE TABLE #DoublePivot

(      [ID] INT IDENTITY(1,1) NOT NULL,

       [UserID] INT NOT NULL,

       [SomeType] INT NOT NULL,

       [NumberA] INT NOT NULL,

       [NumberB] INT NOT NULL,

       [Created] DATETIME NOT NULL DEFAULT(getdate())

)

INSERT INTO #DoublePivot

       (      UserID, SomeType, NumberA, NumberB )

       VALUES

       (      1, 1, 20, 10 ),

       (      1, 2, 50, 20 ),

       (      1, 1, 10, 0),

       (      2, 1, 10, 10),

       (      2, 2, 10, 40),

       (      1, 1, 20, 20),

       (      2, 2, 20, 70);

SELECT * FROM #DoublePivot order by UserID, SomeType

SELECT UserID, SomeType, SUM(NumberA) as SumNumberA, SUM(NumberB) as SumNumberB FROM #DoublePivot Group by UserID, SomeType order by UserID, SomeType

                                 

       SELECT UserID,

                     MAX([1]) AS SomeType1_NumberA,

                     MAX([-1]) AS SomeType1_NumberB,

                     MAX([2]) AS SomeType1_NumberB ,

                     MAX([-2]) AS SomeType2_NumberB

       FROM

                     (      SELECT UserID,

                                         SomeType,

                                         (-1 * SomeType) as SomeTypeB,

                                         SUM(NumberA) AS NumberA,

                                         SUM(NumberB) AS NumberB

                           FROM #DoublePivot

                           GROUP BY UserID, SomeType

                     )  BaseTable

                     PIVOT

                     (      SUM(NumberA)

                           FOR SomeType IN ([1], [2])

                     ) AS PivotA

                     PIVOT

                     (      SUM(NumberB)

                           FOR SomeTypeB IN ([-1], [-2])

                     ) AS PivotB

       GROUP BY UserID

DROP TABLE #DoublePivot

*  (-1 * SomeType) - in order to set a new "base" column to the second pivot summary.

Pivot On Multiple Columns

------------------------------------------------------------------------------

-- temp table for the example

CREATE TABLE tmpResults

( CustomerType nvarchar(50),

SomeTypeLetter nvarchar(50),

NumOfCustomers int

)

INSERT INTO tmpResults (CustomerType, SomeTypeLetter, NumOfCustomers)

VALUES ('CustomerType1', 'SomeTypeA', 33),

('CustomerType1', 'SomeTypeB', 12),

('CustomerType1', 'SomeTypeC', 61),

('CustomerType2', 'SomeTypeA', 11),

('CustomerType2', 'SomeTypeB', 25),

('CustomerType2', 'SomeTypeC', 21),

('CustomerType3', 'SomeTypeA', 17),

('CustomerType3', 'SomeTypeB', 38),

('CustomerType3', 'SomeTypeC', 9),

('CustomerType4', 'SomeTypeA', 5),

('CustomerType4', 'SomeTypeB', 91),

('CustomerType4', 'SomeTypeC', 12)

--SELECT * FROM tmpResults

------------------------------------------------------------------------------

--Pivot On Multiple Columns:

SELECT [CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

FROM 

(   SELECT  isnull(p.NumOfCustomers,0) as NumOfCustomers,

(p.CustomerType) + '-' + (p.SomeTypeLetter) as [desc]

FROM    tmpResults p

) [base]

PIVOT

(   SUM(NumOfCustomers)

FOR [desc] IN

(

[CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

)

) [pvt]

DROP TABLE tmpResults

PIVOT

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 

[0], [1], [2], [3], [4]

FROM

(SELECT DaysToManufacture, StandardCost 

    FROM Production.Product) SourceTable

PIVOT

(

AVG(StandardCost)

FOR DaysToManufacture IN ([0], [1], [2], [3], [4])

) PivotTable

Notes:

• Available from SQL Server 2005.
• When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.
• Pivot columns are specific values from the column of the source table, and can't be declared dynamic.