WITH TEST AS (
  SELECT 'Y' yn1, 'N' yn2, 'a' value FROM DUAL UNION ALL
  SELECT 'Y', 'N', 'b'  FROM DUAL UNION ALL
  SELECT 'Y', 'Y', 'c'  FROM DUAL UNION ALL
  SELECT 'N', 'Y', 'd'  FROM DUAL UNION ALL
  SELECT 'N', 'Y', 'e'  FROM DUAL
)
SELECT *
  FROM (
          SELECT SUBSTR ( MAX ( SYS_CONNECT_BY_PATH ( value, ',' )), 2 )
            FROM (
                  SELECT yn1
                       , yn2
                       , value
                       , row_number () over ( order by value ) rn
                    FROM test
                   WHERE yn1 = 'Y'
          )
          START WITH rn = 1
          CONNECT BY PRIOR rn = rn - 1
                 AND PRIOR yn1 = yn1
) a
,
(
          SELECT SUBSTR ( MAX ( SYS_CONNECT_BY_PATH ( value, ',' )), 2 )
            FROM (
                  SELECT yn1
                       , yn2
                       , value
                       , row_number () over ( order by value ) rn
                    FROM test
                   WHERE yn2 = 'Y'
          )
          START WITH rn = 1
          CONNECT BY PRIOR rn = rn - 1
                 AND PRIOR yn2 = yn2
)

답변 감사합니다.

정말 도움이 되네요 ㅜㅜ

정말 감사합니다

WITH t AS
(
SELECT 'Y' yn1, 'N' yn2, 'a' v FROM dual
UNION ALL SELECT 'Y', 'N', 'b' FROM dual
UNION ALL SELECT 'Y', 'Y', 'c' FROM dual
UNION ALL SELECT 'N', 'Y', 'd' FROM dual
UNION ALL SELECT 'N', 'Y', 'e' FROM dual
)
SELECT RTRIM(XMLAGG(XMLELEMENT(x, v1, ',') ORDER BY v1).EXTRACT('//text()'), ',') v1
     , RTRIM(XMLAGG(XMLELEMENT(x, v2, ',') ORDER BY v2).EXTRACT('//text()'), ',') v2
  FROM (SELECT DECODE(yn1, 'Y', v) v1
             , DECODE(yn2, 'Y', v) v2
          FROM t
        )
;

마농 // XMLAGG 이 함수 9i에서도 되는거에여? 10g 부터 되는줄 알았네 ㅎㅎ