Oracle 一行转多行(二)

最新推荐文章于 2024-08-17 08:00:00 发布

白露-

最新推荐文章于 2024-08-17 08:00:00 发布

阅读量736

点赞数

分类专栏： Oracle汇总笔记文章标签： oracle sql

本文链接：https://blog.csdn.net/m0_56227168/article/details/121008637

版权

Oracle汇总笔记专栏收录该内容

20 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在Oracle中通过SQL进行一行数据拆分成多行，包括使用substr和instr函数的技巧，以及在进阶场景下同时处理两个或更多列的拆分。通过实例演示了复杂的数据拆分过程，适合数据库开发者深入理解和实践.

摘要由CSDN通过智能技术生成

一.一行转多行

一行数据
使用如下语句拆分:

SELECT substr(a, instr(a, ',', 1, levels.lvl) + 1, instr(a, ',', 1, levels.lvl + 1) -(instr(a, ',', 1, levels.lvl) + 1)) as a,t1.you_col,t1.sort
FROM
(SELECT ',' || you_col || ',' AS a,length(you_col) - nvl(length(REPLACE(you_col, ',')), 0) + 1 AS cnt,t.you_col,t.sort FROM you_table t) t1,
(SELECT rownum AS lvl FROM (SELECT MAX(length(you_col || ',') - nvl(length(REPLACE(you_col, ',')), 0)) max_len FROM you_table ) CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= t1.cnt

多行数据

二.进阶

一行数据
使用如下语句拆分:

SELECT 
substr(a, instr(a, ',', 1, levels.lvl) + 1, instr(a, ',', 1, levels.lvl + 1) -(instr(a, ',', 1, levels.lvl) + 1)) as a,
substr(b, instr(b, ',', 1, levels2.lvl) + 1, instr(b, ',', 1, levels2.lvl + 1) -(instr(b, ',', 1, levels2.lvl) + 1)) as b,
t1.you_col,t1.you_col2,t1.sort
FROM
(SELECT ',' || you_col || ',' AS a,length(you_col) - nvl(length(REPLACE(you_col, ',')), 0) + 1 AS cnt,',' || you_col2 || ',' AS b,length(you_col2) - nvl(length(REPLACE(you_col2, ',')), 0) + 1 AS cnt2,t.you_col,t.you_col2,t.sort FROM you_table t) t1,
(SELECT rownum AS lvl FROM (SELECT MAX(length(you_col || ',') - nvl(length(REPLACE(you_col, ',')), 0)) max_len FROM you_table ) CONNECT BY LEVEL <= max_len) levels,
(SELECT rownum AS lvl FROM (SELECT MAX(length(you_col2 || ',') - nvl(length(REPLACE(you_col2, ',')), 0)) max_len FROM you_table ) CONNECT BY LEVEL <= max_len) levels2
WHERE levels.lvl <= t1.cnt and levels2.lvl <= t1.cnt2