WITH T1 AS
(
SELECT '001' COL1, '005' COL2, '006' COL3
, NULL COL4, NULL COL5, NULL COL6, NULL COL7
, NULL COL8, NULL COL9, '010' COL10 FROM DUAL
)
, T2 AS
(
SELECT '001' CD, '공공일' CDNM FROM DUAL UNION ALL
SELECT '005' CD, '공공오' CDNM FROM DUAL UNION ALL
SELECT '006' CD, '공공육' CDNM FROM DUAL UNION ALL
SELECT '010' CD, '공일공' CDNM FROM DUAL
)
--pivot
SELECT VAL, CDNM
FROM
(
SELECT * FROM T1
UNPIVOT (VAL FOR GB IN(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10))
) A, T2 B
WHERE A.VAL = B.CD

--행복사
SELECT VAL, CDNM
FROM
(
SELECT DECODE(LV,1,COL1,2,COL2,3,COL3,4,COL4,5,COL5,6,COL6,7,COL7,8,COL8,9,COL9,10,COL10) VAL 
FROM T1
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10)
) A, T2 B
WHERE A.VAL = B.CD

WITH code_t AS
(
SELECT '001' cd, '공공일' nm FROM dual
UNION ALL SELECT '002', '공공이' FROM dual
UNION ALL SELECT '005', '공공오' FROM dual
UNION ALL SELECT '006', '공공육' FROM dual
UNION ALL SELECT '010', '공일공' FROM dual
UNION ALL SELECT '100', '일공공' FROM dual
)
, data_t(pk, cd1, cd2, cd3, cd4, cd5, cd6, cd7, cd8, cd9, cd10) AS
(
SELECT 1, '001', '005', '006', '100', '', '', '', '', '', '' FROM dual
UNION ALL SELECT 2, '001', '002', '010', '', '', '', '', '', '', '' FROM dual
)
SELECT a.pk
     , b.cd
     , b.nm
  FROM data_t a
     , code_t b
 WHERE b.cd IN (a.cd1, cd2, cd3, cd4, cd5, cd6, cd7, cd8, cd9, cd10)
 ORDER BY a.pk, b.cd
;