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.

"Automatic" extended events - system_health

After SQL Server installation, A default extended events session - system health – is created.

System health runs without any noticeable performance effects, and collect some important events:

• Deadlocks
• Sessions that have waited on locks, waits, etc.
• More….

See all in:

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session

I use it to catch deadlocks graph, and it was very helpful!

You can also filters events.

If you don’t remember the exact event name, you can type part of it:

After you get the results and see the event you need, you can specify the filter by it: