oracle 列行转换

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)。

转载于:https://www.cnblogs.com/Jiphen/p/5916267.html