Pivot Table in Oracle?

Question

I have a table with many columns which specifies if an error is valid or not. I need output in such a way that columns names which specify the error as rows and count of valid and invalid fields for each error.

Example of similar input and expected output below

id   name            txn_column_name  flag1 flag2 flag3 
1    Resident Count  resident_ct      Y     Y     N
2    Incident Count  incident_ct      Y     Y     Y
3    Facility Status facil_stat       N     N     N

Output required

Col1_name Y_Count    N_Count
flag1       2            1
flag2       2            1
flag3       1            2

Answer 1

We can try using Oracle's UNPIVOT operator here:

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;

Demo

Another straightforward option uses a series of unions:

SELECT
    'flag1' AS Col1_name,
    COUNT(CASE WHEN flag1 = 'Y' THEN 1 END) AS Y_Count,
    COUNT(CASE WHEN flag1 = 'N' THEN 1 END) AS N_Count
FROM yourTable
UNION ALL
SELECT 'flag2', COUNT(CASE WHEN flag2 = 'Y' THEN 1 END),
    COUNT(CASE WHEN flag2 = 'N' THEN 1 END)
FROM yourTable
UNION ALL
SELECT 'flag3', COUNT(CASE WHEN flag3 = 'Y' THEN 1 END),
    COUNT(CASE WHEN flag3 = 'N' THEN 1 END)
FROM yourTable
ORDER BY 1;

Demo