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,
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.