[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

未分割分号时数据

按分号分割后