By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 1 AS id, 'Resident Count' AS name, 'resident_ct' AS txn_column_name, 'Y' AS flag1, 'Y' AS flag2, 'N' AS flag3 FROM dual UNION ALL
SELECT 2, 'Incident Count', 'incident_ct', 'Y', 'Y', 'Y' FROM dual UNION ALL
SELECT 3, 'Facility Status', 'facil_stat', 'N', 'N', 'N' FROM dual
)
SELECT
flag AS Col1_name,
COUNT(CASE WHEN val = 'Y' THEN 1 END) AS Y_Count,
COUNT(CASE WHEN val = 'N' THEN 1 END) AS N_Count
FROM yourTable
UNPIVOT
(
val FOR (flag) IN (
flag1 AS 'flag1',
flag2 AS 'flag2',
flag3 AS 'flag3'
)
)
GROUP BY
flag
ORDER BY
flag
| COL1_NAME | Y_COUNT | N_COUNT |
|---|---|---|
| flag1 | 2 | 1 |
| flag2 | 2 | 1 |
| flag3 | 1 | 2 |