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.