데이터베이스 질문과 답변
빈셀에 값을 채우는 방법이 생각나지 않아 도움을 요청 드려요
WITH T
AS (SELECT '강호동' AS USER_NM,
1 AS LEV,
'a' CD,
'' AS UP_CD
FROM DUAL
UNION ALL
SELECT '유재석' AS USER_NM,
2 AS LEV,
'b' CD,
'a' AS UP_CD
FROM DUAL
UNION ALL
SELECT '홍길동' AS USER_NM,
3 AS LEV,
'c' CD,
'b' AS UP_CD
FROM DUAL
UNION ALL
SELECT '강감찬' AS USER_NM,
4 AS LEV,
'd' CD,
'c' AS UP_CD
FROM DUAL)
SELECT MAX (DECODE (A.LEV, 1, A.USER_NM)) NM1,
MAX (DECODE (A.LEV, 2, A.USER_NM)) NM2,
MAX (DECODE (A.LEV, 3, A.USER_NM)) NM3,
MAX (DECODE (A.LEV, 4, A.USER_NM)) NM4,
B.LEV,
CD,
UP_CD
FROM T A,
( SELECT LEVEL LEV
FROM DUAL
CONNECT BY LEVEL <= 4) B
WHERE A.LEV <= B.LEV
GROUP BY B.LEV, CD, UP_CD
ORDER BY CD
현재 데이터의 추출은 아래와 같습니다.
| nm | lev | cd | up_cd |
| 강호동 | 1 | a | |
| 유재석 | 2 | b | a |
| 홍길동 | 3 | c | b |
| 강감찬 | 4 | d | c |
하고자 변경하고자 하는 데이터 추출은 아래와 같습니다.
| nm1 | nm2 | nm3 | nm4 | lev |
| 강호동 | 1 | |||
| 강호동 | 유재석 | 2 | ||
| 강호동 | 유재석 | 홍길동 | 3 | |
| 강호동 | 유재석 | 홍길동 | 강감찬 | 4 |
WITH T AS
(
SELECT '강호동' AS USER_NM, 1 AS LEV FROM DUAL
UNION ALL
SELECT '유재석' AS USER_NM, 2 AS LEV FROM DUAL
UNION ALL
SELECT '홍길동' AS USER_NM, 3 AS LEV FROM DUAL
UNION ALL
SELECT '강감찬' AS USER_NM, 4 AS LEV FROM DUAL
)
SELECT
MAX(DECODE(A.LEV,1,A.USER_NM)) NM1
, MAX(DECODE(A.LEV,2,A.USER_NM)) NM2
, MAX(DECODE(A.LEV,3,A.USER_NM)) NM3
, MAX(DECODE(A.LEV,4,A.USER_NM)) NM4
, B.LEV
FROM T A
,(SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL <= 4) B
WHERE A.LEV <= B.LEV
GROUP BY B.LEV
음 이게 up cd 가 있어 서로 계층으로 가능한데 lev 되로 하니까 예제처럼 쿼리가 안나오네요 ... 혹시 up cd 로 하는 방법이 있을가요?
예시는 지금 현재 이렇게 나오고 있습니다 ㅜㅜ
/* Formatted on 2017-09-21 오전 10:07:14 (QP5 v5.163.1008.3004) */
WITH T
AS (SELECT '강호동' AS USER_NM,
1 AS LEV,
'a' CD,
'' AS UP_CD
FROM DUAL
UNION ALL
SELECT '유재석' AS USER_NM,
2 AS LEV,
'b' CD,
'a' AS UP_CD
FROM DUAL
UNION ALL
SELECT '홍길동' AS USER_NM,
3 AS LEV,
'c' CD,
'b' AS UP_CD
FROM DUAL
UNION ALL
SELECT '강감찬' AS USER_NM,
4 AS LEV,
'd' CD,
'c' AS UP_CD
FROM DUAL)
SELECT MAX (DECODE (A.LEV, 1, A.USER_NM)) NM1,
MAX (DECODE (A.LEV, 2, A.USER_NM)) NM2,
MAX (DECODE (A.LEV, 3, A.USER_NM)) NM3,
MAX (DECODE (A.LEV, 4, A.USER_NM)) NM4,
B.LEV,
CD,
UP_CD
FROM T A,
( SELECT LEVEL LEV
FROM DUAL
CONNECT BY LEVEL <= 4) B
WHERE A.LEV <= B.LEV
GROUP BY B.LEV, CD, UP_CD
ORDER BY CD
WITH t AS
(
SELECT '강호동' user_nm, 1 lev, 'a' cd, '' up_cd FROM dual
UNION ALL SELECT '유재석', 2, 'b', 'a' FROM dual
UNION ALL SELECT '홍길동', 3, 'c', 'b' FROM dual
UNION ALL SELECT '강감찬', 4, 'd', 'c' FROM dual
)
SELECT REGEXP_SUBSTR(nm, '[^-]+', 1, 1) nm_1
, REGEXP_SUBSTR(nm, '[^-]+', 1, 2) nm_2
, REGEXP_SUBSTR(nm, '[^-]+', 1, 3) nm_3
, REGEXP_SUBSTR(nm, '[^-]+', 1, 4) nm_4
, lev
FROM (SELECT SYS_CONNECT_BY_PATH(user_nm, '-') nm
, lev
FROM t
START WITH up_cd IS NULL
CONNECT BY PRIOR cd = up_cd
)
;
감사합니다 마농님..
쿼리랑 내용 추가하셨네요ㅎㅎ 첨부터 자세히 적어주시면 좋았을텐데ㅎㅎ
죄송합니다 너무 정보를 덜 적었네요 ㅜㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.