WITH CTE_Pivot AS (
SELECT
State,
[Junior],
[Senior],
[Junior]+[Senior] AS [All Comps],
[Cricket Blast],
[Daughters & Dads],
[Pop-Up Blast],
[School Holiday],
[WWCF],
[Cricket Blast]+[Daughters & Dads]+[Daughters & Dads]+[Pop-Up Blast]+[School Holiday]+[WWCF] as [All Program]
FROM (
SELECT
distinct
unique_id,
State,
CASE
WHEN program IN (‘CRICKET_BLAST’,’Master Blaster’,’ALL_GIRLS_CRICKET_BLAST’,’Junior Blaster’) THEN ‘Cricket Blast’
WHEN program LIKE ‘%Daughters and Dads%’ THEN ‘Daughters & Dads’
WHEN program LIKE ‘%Pop Up Program%’ THEN ‘Pop-Up Blast’
WHEN program LIKE ‘%Holiday Program%’ THEN ‘School Holiday’
WHEN program LIKE ‘%WWCF%’ THEN ‘WWCF’
END AS Program,
CASE
WHEN ProgramCategory LIKE ‘%Junior Cricket%’ THEN ‘Junior’
WHEN ProgramCategory LIKE ‘%Senior Cricket%’ THEN ‘Senior’
END AS ProgramCategory
FROM [DMP].[FCT_CC_PlayersRegistrationEngagementStatus]
WHERE state IS NOT NULL
) g
PIVOT (
COUNT(unique_id) FOR
Program IN ([Cricket Blast], [Daughters & Dads], [Pop-Up Blast], [School Holiday], [WWCF])
) AS pvt1