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.行轉列

未經處理資料：

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 列行轉換