首页 > 代码库 > oracle 一行转多行

oracle 一行转多行

比如sql:

select zyxdm from table where bindid=2265254

查询结果为:1|4|8|9|10

将这个查询结果转成多行,结果如下:
ID
1
4
8
9
10

  转换的sql :

with CTE1 as (select zyxdm from table where bindid=2265254)
SELECT REGEXP_SUBSTR((select * from CTE1), ‘[^|]+‘, 1, ROWNUM) id
FROM DUAL CONNECT BY ROWNUM <= LENGTH((select * from CTE1))
- LENGTH(REPLACE((select * from CTE1), ‘|‘, ‘‘)) + 1

  

oracle 一行转多行