WITH T AS (SELECT 1 ID, 1 FWFS, '100,102' CLR FROM DUAL UNION ALL SELECT 2 ID, 1 FWFS, '100,101,102' CLR FROM DUAL UNION ALL SELECT 3 ID, 1 FWFS, '103' CLR FROM DUAL UNION ALL SELECT 4 ID, 2 FWFS, '102,103' CLR FROM DUAL) SELECT CLR, SUM(CLFS1), SUM(CLFS2), SUM(TOTAL) FROM (SELECT CLR, DECODE(T2.FWFS, 1, CN, 0) CLFS1, DECODE(T2.FWFS, 2, CN, 0) CLFS2, DECODE(T2.FWFS, 1, CN, 0) + DECODE(T2.FWFS, 2, CN, 0) TOTAL FROM (SELECT CLR, FWFS, COUNT(*) CN FROM (SELECT ID, FWFS, REGEXP_SUBSTR(CLR, '[^,]+', 1, LEVEL) CLR FROM T CONNECT BY LEVEL <= REGEXP_COUNT(CLR, ',') + 1 AND ID = PRIOR ID AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1 GROUP BY T1.CLR, FWFS) T2) T3 GROUP BY T3.CLR ORDER BY T3.CLR;
WITH T AS
(SELECT 1 ID, 1 FWFS, '100,102' CLR
FROM DUAL
UNION ALL
SELECT 2 ID, 1 FWFS, '100,101,102' CLR
FROM DUAL
UNION ALL
SELECT 3 ID, 1 FWFS, '103' CLR
FROM DUAL
UNION ALL
SELECT 4 ID, 2 FWFS, '102,103' CLR
FROM DUAL)
SELECT CLR, SUM(CLFS1), SUM(CLFS2), SUM(TOTAL)
FROM (SELECT CLR,
DECODE(T2.FWFS, 1, CN, 0) CLFS1,
DECODE(T2.FWFS, 2, CN, 0) CLFS2,
DECODE(T2.FWFS, 1, CN, 0) + DECODE(T2.FWFS, 2, CN, 0) TOTAL
FROM (SELECT CLR, FWFS, COUNT(*) CN
FROM (SELECT ID,
FWFS,
REGEXP_SUBSTR(CLR, '[^,]+', 1, LEVEL) CLR
FROM T
CONNECT BY LEVEL <= REGEXP_COUNT(CLR, ',') + 1
AND ID = PRIOR ID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1
GROUP BY T1.CLR, FWFS) T2) T3
GROUP BY T3.CLR
ORDER BY T3.CLR;