How can I pivot with several counts

Question

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

Answer 1

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