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

oracle 列行转换-LMLPHP

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;

oracle 列行转换-LMLPHP

3.行转列 WM_CONCAT

原始数据:

oracle 列行转换-LMLPHP

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;

结果:

oracle 列行转换-LMLPHP

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;

oracle 列行转换-LMLPHP

总结:LISTAGG性能优于WM_CONCAT,但LISTAGG不支持去重(可以先去重,再转换),WM_CONCAT可以去重,用法WM_CONCAT(DISTINCT COLUMN2)。

04-15 14:37