1.列转换 1:每个字母转成一行
SELECT SUBSTR(A.COLUMN1, LEV, 1) COLUMN1
FROM (
SELECT 'AABDC' COLUMN1 FROM DUAL
) A,
(SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL<=100) B
WHERE LEV <= LENGTH(A.COLUMN1);
2.通过“,”通过列转行
SELECT COLUMN1, REGEXP_SUBSTR(A.COLUMN2 ,'[^,]+', 1, LEV) COLUMN2
FROM (
SELECT '001' AS COLUMN1, '0,2,3' COLUMN2 FROM DUAL
) A,
(SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL<=100) B
WHERE LEV <= LENGTH(A.COLUMN2) - LENGTH(REPLACE(COLUMN2,','))+1;
3.行转列 WM_CONCAT
原始数据：
SELECT T.COLUMN1, MAX(T.COLUMN2) COLUMN2 FROM (
SELECT S.COLUMN1, WM_CONCAT(S.COLUMN2) OVER (PARTITION BY S.COLUMN1 ORDER BY S.COLUMN2) COLUMN2 FROM (
SELECT '001' COLUMN1, '0' COLUMN2 FROM DUAL
UNION ALL SELECT '001' COLUMN1, '3' COLUMN2 FROM DUAL
UNION ALL SELECT '001' COLUMN1, '2' COLUMN2 FROM DUAL
) S
) T GROUP BY T.COLUMN1;
结果：
4.行转列 LISTAGG
SELECT COLUMN1, LISTAGG(COLUMN2, ',') WITHIN GROUP (ORDER BY COLUMN1) FROM (
SELECT '001' COLUMN1, '0' COLUMN2 FROM DUAL
UNION ALL SELECT '001' COLUMN1, '3' COLUMN2 FROM DUAL
UNION ALL SELECT '001' COLUMN1, '2' COLUMN2 FROM DUAL
) T GROUP BY COLUMN1;
总结：LISTAGG性能优于WM_CONCAT，但LISTAGG不支持去重(可以先去重，再转换)，WM_CONCAT可以去重，用法WM_CONCAT(DISTINCT COLUMN2)。