CODEWITHSUNDEEP
sqlsql-serversql-server-2012pivot-table
Jorge Granados
Jorge Granados

Reputation: 1

How can I pivot with several counts

I am trying to monitor some DQ on some ID's and I have a table that looks like this:

ID Flag 1 Flag 2 Flag 3 Date
001 Error January
002 Error Error January
003 Error Error January
001 Error February
002 Error February
003 Error February
001 Error Error Error March
002 March
003 Error Error Error March
001 April
002 Error April
003 Error April

and would like to pivot it like this:

- January February March April
Flag1 2 2 1 0
Flag2 2 1 1 1
Flag3 1 0 1 1

So far I have tried it like this

SELECT * FROM (
  SELECT
    Account, date, Flag1, Flag2 
    from  Main_Table  
    where Flag1 is not null and len(Rule_Account_Name) >1
) Accounts
PIVOT (
  count(Account)
  FOR date in ([2021-08-21],[2021-09-21],[2021-10-22],[2021-11-22],[2021-12-31],[2022-01-31],[2022-02-28])
) AS PivotTable

I appreciate the help, the point would be to monitor the number of errors each month for each of the flags to be able to rank the flags to put more effort in fixing the top 3 worst flags

Upvotes: 0

Views: 104

Answers (1)

Charlieface
Charlieface

Reputation: 71638

You need to first un-pivot the data so that you can get the Flag values into rows, then pivot the months into columns.

SELECT *
FROM (
    SELECT
      [Flag 1],
      [Flag 2],
      [Flag 3],
      Date
    FROM Main_Table mt
) mt
UNPIVOT (
    FlagValue FOR FlagName IN (
        [Flag 1],
        [Flag 2],
        [Flag 3]
    )
) u
PIVOT (
    COUNT(FlagValue) FOR Date IN (
        January,
        February,
        March,
        April
    )
) p;

You can also replace UNPIVOT with CROSS APPLY (VALUES and replace PIVOT with conditional aggregation.

SELECT
  u.FlagName,
  January  = COUNT(CASE WHEN Date = 'January'  THEN FlagValue END),
  February = COUNT(CASE WHEN Date = 'February' THEN FlagValue END),
  March    = COUNT(CASE WHEN Date = 'March'    THEN FlagValue END),
  April    = COUNT(CASE WHEN Date = 'April'    THEN FlagValue END)
FROM Main_Table mt
CROSS APPLY (VALUES
    ('Flag 1', [Flag 1]),
    ('Flag 2', [Flag 2]),
    ('Flag 3', [Flag 3])
) u(FlagName, FlagValue)
GROUP BY
  u.FlagName;

db<>fiddle

Upvotes: 3

Related Questions