WITH A ( id, yn1,yn2,yn3,yn4,yn5,yn6,yn7,yn8,yn9,yn10,yn11,yn12,yn13,yn14,yn15,yn16,yn17,yn18,yn19,yn20) AS ( SELECT '1','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N' FROM DUAL UNION ALL SELECT '2','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','N' FROM DUAL ) SELECT ID , COUNT(CASE WHEN VAL = 'Y' THEN 1 END ) CNT FROM ( SELECT * FROM A UNPIVOT ( VAL FOR YN IN ( yn1,yn2,yn3,yn4,yn5,yn6,yn7,yn8,yn9,yn10,yn11,yn12,yn13,yn14,yn15,yn16,yn17,yn18,yn19,yn20)) ) GROUP BY ID
SELECT id , REGEXP_COUNT( yn1||yn2||yn3||yn4||yn5||yn6||yn7||yn8||yn9||yn10|| yn11||yn12||yn13||yn14||yn15||yn16||yn17||yn18||yn19||yn20 , 'Y') cnt_y FROM a ;
SELECT * FROM a UNPIVOT (yn FOR gb IN (yn1,yn2,yn3,yn4,yn5,yn6,yn7,yn8,yn9,yn10 ,yn11,yn12,yn13,yn14,yn15,yn16,yn17,yn18,yn19,yn20)) PIVOT (COUNT(gb) FOR yn IN ('Y' cnt)) ;