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

示例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 || ',' ) - NVL( LENGTH( REPLACE( VALUENAME, ',' )), 0 )) MAX_LEN
				FROM
					TESTTABLE
			)
		CONNECT BY
			LEVEL <= MAX_LEN
	) LEVELS
WHERE
	LEVELS.LVL <= A.CNT
	--笛卡尔连接
ORDER BY
	GROUPNAME

SELECT
	REGEXP_SUBSTR( q.nums, '[^,]+', 1, rownum, 'i' ) AS nums,
	names
FROM
	(
		SELECT
			'1,2,3' nums,
			'张三' names
		FROM
			dual
	) q
CONNECT BY
	rownum <= LENGTH(q.nums) - LENGTH(
		REGEXP_REPLACE( q.nums, ',', '' )
	) + 1;