SELECT CD,
MAX(DECODE(RUM, 1, NO, NULL)) AS NO1,
MAX(DECODE(RUM, 2, NO, NULL)) AS NO2,
MAX(DECODE(RUM, 3, NO, NULL)) AS NO3,
MAX(DECODE(RUM, 4, NO, NULL)) AS NO4,
MAX(DECODE(RUM, 5, NO, NULL)) AS NO5,
MAX(DECODE(RUM, 6, NO, NULL)) AS NO6
FROM (
SELECT CD,
ROW_NUMBER() OVER (PARTITION BY CD ORDER BY LV) AS RUM,
DECODE(LV, 1, NO1, 2, NO2, 3, NO3, 4, NO4, 5, NO5, 6, NO6) AS NO
FROM (
SELECT CD, NO1, NO2, NO3, NO4, NO5, NO6
FROM T
),
(
SELECT LEVEL LV
FROM DUAL
CONNECT BY LEVEL <= 6
)
WHERE DECODE(LV, 1, NO1, 2, NO2, 3, NO3, 4, NO4, 5, NO5, 6, NO6) IS NOT NULL
)
GROUP BY CD
ORDER BY CD

cd가 E인게 안나오네요..

SELECT cd
, REGEXP_SUBSTR(no, '[^,]+', 1, 1) no1
, REGEXP_SUBSTR(no, '[^,]+', 1, 2) no2
, REGEXP_SUBSTR(no, '[^,]+', 1, 3) no3
, REGEXP_SUBSTR(no, '[^,]+', 1, 4) no4
, REGEXP_SUBSTR(no, '[^,]+', 1, 5) no5
, REGEXP_SUBSTR(no, '[^,]+', 1, 6) no6
FROM
(
SELECT cd
, no1||','||no2||','||no3||','||no4||','||no5||','||no6 no
FROM t
)
;

SELECT
CD,
MIN(DECODE(GB3, 1, NO)) NO1
, MIN(DECODE(GB3, 2, NO)) NO2
, MIN(DECODE(GB3, 3, NO)) NO3
, MIN(DECODE(GB3, 4, NO)) NO4
, MIN(DECODE(GB3, 5, NO)) NO5
, MIN(DECODE(GB3, 6, NO)) NO6
FROM (
SELECT
CD,
NO,
ROW_NUMBER() OVER(PARTITION BY CD ORDER BY NO) GB3
FROM (
SELECT
CD,
DECODE(GB2, 1, NO1, 2, NO2, 3, NO3, 4,NO4, 5, NO5, 6, NO6) NO
FROM (
SELECT
CD, NO1, NO2, NO3, NO4, NO5, NO6
FROM T
)
, (SELECT LEVEL GB2 FROM DUAL CONNECT BY LEVEL <=6 )
)
)
GROUP BY CD

마농님하고 뜨리뜨리님이 답변을 달아주셨네요^^

제 답변은
SELECT CD,
MAX(DECODE(RN,1,NOM)) NO1,
MAX(DECODE(RN,2,NOM)) NO2,
MAX(DECODE(RN,3,NOM)) NO3,
MAX(DECODE(RN,4,NOM)) NO4,
MAX(DECODE(RN,5,NOM)) NO5,
MAX(DECODE(RN,6,NOM)) NO6
FROM (
SELECT A.CD,
A.NOM,
ROW_NUMBER() OVER (PARTITION BY A.CD ORDER BY A.NUM) RN
FROM (
SELECT A.CD,
C.NUM,
DECODE(C.NUM,1,A.NO1,2,A.NO2,3,A.NO3,4,A.NO4,5,A.NO5,6,A.NO6) NOM
FROM T A,(SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL <= 6) C
) A PARTITION BY (A.CD) RIGHT OUTER JOIN DUAL B
ON A.NOM IS NOT NULL
)
GROUP BY CD
ORDER BY CD ;

마농님이 달아준 답변에서 새로운걸 알았네요. 일반정규식에서 null인 값은 자동으로 skip이 되더군요.. 당연히 안될줄알고 일반정규식으로 풀 생각은 안했었는데..

우잉... 섭스에도 정규식 쓸 수 있네... 우히힛. + ㅁ +)/