在遇到一些字典表时出现这样的数据表:
想把它转成:
那就不妨试试这个方法:
SELECT DISTINCT (SUBSTR(T.RPT_ID,
INSTR(T.RPT_ID, ',', 1, C.LV) + 1,
INSTR(T.RPT_ID, ',', 1, C.LV + 1) -
(INSTR(T.RPT_ID, ',', 1, C.LV) + 1))) AS XZQH,
DLDM
FROM (SELECT ',' || XZQH || ',' RPT_ID,
LENGTH(XZQH || ',') - NVL(LENGTH(REPLACE(XZQH, ',')), 0) CNT,
DLDM
FROM TTABLE) T,
(SELECT LEVEL LV
FROM TTABLE
CONNECT BY LEVEL <=
LENGTH(XZQH || ',') - NVL(LENGTH(REPLACE(XZQH, ',')), 0)) C
WHERE T.CNT >= C.LV