ORACLE知识点（八）：ORACLE单行转多行

原创

已于 2022-12-07 16:28:25 修改 · 2.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #sql

于 2019-06-10 11:35:05 首次发布

示例SQL：

WITH TESTTABLE AS (
	SELECT
		'TEST1' GROUPNAME ,
		'A,B,C,D,E' VALUENAME,
		'1,2,3,4,5' VALUEID
	FROM
		DUAL
UNION ALL SELECT
		'TEST2' GROUPNAME ,
		'AA,BB,CC,DD' VALUENAME,
		'11,22,33,44' VALUEID
	FROM
		DUAL
) SELECT
	GROUPNAME,
	SUBSTR( A.VALUENAME, INSTR( A.VALUENAME, ',', 1, LEVELS.LVL ) + 1, INSTR( A.VALUENAME, ',', 1, LEVELS.LVL + 1 ) -( INSTR( A.VALUENAME, ',', 1, LEVELS.LVL ) + 1 )) AS VALUENAME ,
	SUBSTR( A.VALUEID, INSTR( A.VALUEID, ',', 1, LEVELS.LVL ) + 1, INSTR( A.VALUEID, ',', 1, LEVELS.LVL + 1 ) -( INSTR( A.VALUEID, ',', 1, LEVELS.LVL ) + 1 )) AS VALUEID
FROM
	(
		SELECT
			GROUPNAME,
			',' || VALUENAME || ',' AS VALUENAME,
			',' || VALUEID || ',' AS VALUEID,
			LENGTH(VALUENAME) - NVL( LENGTH( REPLACE( VALUENAME, ',' )), 0 ) + 1 AS CNT
			--取待拆分字段每行按照分隔符','分割后的记录数，用于CONNECT BY
		FROM
			TESTTABLE
	) A,
	(
		SELECT
			ROWNUM AS LVL
			--产生一个待拆分字段分割后最大记录数的序列
		FROM
			(
				SELECT
					MAX( LENGTH( VALUENAME || ',' ) - N