[oracle]逗号(或分号等)间隔的列,按这一列劈分后转多行
原文转自 [oracle]逗号(或分号等)间隔的列,按这一列劈分后转多行
SELECT cp.ES_EXTNUMBER,
cp.ES_AGENTID,
cp.ES_CHSTATESTR,
(sysdate - cp.ES_STARTDATETIME) * 24 * 3600 es_times,
REGEXP_SUBSTR(CP.ES_GROUP, '[^;]+', 1, L) AS ES_GROUP,
L
FROM aladdinpbx_extstate CP,
(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 10)
WHERE L(+) <= LENGTH(CP.ES_GROUP) - LENGTH(REPLACE(CP.ES_GROUP, ';')) + 1
order by ES_EXTNUMBER
未分割分号时数据
按分号分割后
