데이터베이스 질문과 답변
안녕하세요
with t as
(
select '1,2,4' code from dual
union all select'2' from dual
union all select'1,3,4' from dual
)
select code
, replace(replace(code,'1','학생'),'2','담임') txt
from t;
code : 1:학생,2:담임,3:교직원,4:비회원.......
code가 10개쯤 되는데 replace를 10번 사용은 좀 .....
by 마농
[2018.04.26 17:16:59]
WITH data_t AS
(
SELECT 1 pk, '1,2,4' code FROM dual
UNION ALL SELECT 2, '2' FROM dual
UNION ALL SELECT 3, '1,3,4' FROM dual
)
, code_t AS
(
SELECT '1' code, '학생' name FROM dual
UNION ALL SELECT '2', '담임' FROM dual
UNION ALL SELECT '3', '교직원' FROM dual
UNION ALL SELECT '4', '비회원' FROM dual
)
SELECT a.pk
, a.code
, LISTAGG(b.name, ',') WITHIN GROUP(
ORDER BY INSTR(','||a.code||',', ','||b.code||',')) name
FROM data_t a
, code_t b
WHERE INSTR(','||a.code||',', ','||b.code||',') > 0
GROUP BY a.pk, a.code
;
by 조신부리
[2018.04.26 18:36:51]
제업무 다 해주시네요^^.
) 버튼을 클릭하여 작성 하시면 됩니다.