누적형 코드 데이터를 계층으로 조회

대중소 코드가 누적되어서 들어가 있는 코드 분류체계라면 connect by 를 통해서 계층형으로 조회하는 SQL은 아래와 같이 만들 수 있다,

WITH TBL_DATA AS (
SELECT 'L' AS DEP, '1' AS CD, '동물' AS CD_NM FROM DUAL
UNION SELECT 'M' AS DEP, '10' AS CD, '포유류' AS CD_NM FROM DUAL
UNION SELECT 'M' AS DEP, '11' AS CD, ' 어류' AS CD_NM FROM DUAL
UNION SELECT 'S' AS DEP, '1001' AS CD, '곰' AS CD_NM FROM DUAL
UNION SELECT 'S' AS DEP, '1101' AS CD, '고등어' AS CD_NM FROM DUAL
UNION SELECT 'S' AS DEP, '1102' AS CD, '참치' AS CD_NM FROM DUAL
UNION SELECT 'L' AS DEP, '2' AS CD, '식품' AS CD_NM FROM DUAL
UNION SELECT 'M' AS DEP, '20' AS CD, '빵' AS CD_NM FROM DUAL
UNION SELECT 'S' AS DEP, '2001' AS CD, '브라우니' AS CD_NM FROM DUAL
UNION SELECT 'S' AS DEP, '2002' AS CD, '초코빵' AS CD_NM FROM DUAL
)
SELECT LPAD(' ', (LEVEL - 1) * 5) || CD_NM || ' (' || CD || ')'
FROM (SELECT CASE WHEN LENGTH(CD) = 1 THEN ''
WHEN LENGTH(CD) = 2 THEN SUBSTR(CD, 1, 1)
WHEN LENGTH(CD) = 4 THEN SUBSTR(CD, 1, 2)
END UPPER_CD
, CD
, CD_NM
FROM TBL_DATA
WHERE DEP IN ('L','M','S')
) A
CONNECT BY PRIOR CD = UPPER_ CD
START WITH UPPER_CD IS NULL