重溫SQL行轉列，性能又雙叒提升了

📢📢📢📣📣📣 哈喽！大家好，我是【IT邦德】，10年DBA工作經驗

一位上進心十足的【大資料領域部落客】！😜😜😜

中國DBA聯盟(ACDU)成員，目前從事DBA及程式程式設計，B站及騰訊課堂講師，直播量破10W

擅長主流資料Oracle、MySQL、PG 運維開發，備份恢複，安裝遷移，性能優化、故障應急處理等。

✨ 如果有對【資料庫】感興趣的【小可愛】，歡迎關注【IT邦德】💞💞💞

❤️❤️❤️感謝各位大可愛小可愛！❤️❤️❤️

文章目錄

• ​​前言​​

• ​​🍁 一、列轉行​​

• ​​🍃 1.1 UNION ALL​​
• ​​🍃 1.2 insert all into ... select​​
• ​​🍃 1.3 MODEL​​

• ​​🍁 二、行轉列​​

• ​​🍃 2.1 max+decode​​

• ​​🍁 三、多列轉換成字元串​​
• ​​🍁 四、多行轉換成字元串​​

• ​​4.1 ROW_NUMBER + LEAD​​

前言

最近粉絲們提的問題都與行列轉換有關系，是以我對行列轉換的相關知識做了一個總結，希望對 大家有所幫助，同時有何錯疏，懇請大家指出，我也是在寫作過程中學習，算是一起和大家學習吧

行列轉換包括以下六種情況：

1. 列轉行
2. 行轉列
3. 多列轉換成字元串
4. 多行轉換成字元串
5. 字元串轉換成多列
6. 字元串轉換成多行

🍁 一、列轉行

簡單的說就是将原表中的列名作為轉換後的表的内容，這就是列轉行

🍃 1.1 UNION ALL

create table TEST_JEM
(
NAME VARCHAR2(255),
JANUARY NUMBER(18),
FEBRUARY NUMBER(18),
MARCH NUMBER(18),
APRIL NUMBER(18),
MAY NUMBER(18)
);

insert into TEST_JEM(NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
values ('長壽', 58, 12, 26, 18, 269);
insert into TEST_JEM(NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
values ('璧山', 33, 18, 17, 16, 206);
insert into TEST_JEM(NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
values ('楊家坪', 72, 73, 79, 386, 327);
insert into TEST_JEM(NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
values ('巫溪', 34, 9, 7, 21, 33);
insert into TEST_JEM(NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
values ('豐都', 62, 46, 39, 36, 91);
insert into TEST_JEM(NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
values ('武隆', 136, 86, 44, 52, 142);

commit;      

SELECT * FROM TEST_JEM;

⛳️ 行轉列如下

SELECT *
  FROM (SELECT t.name, 'january' MONTH, t.january v_num
          FROM TEST_JEM t
        UNION ALL
        SELECT t.name, 'february' MONTH, t.february v_num
          FROM TEST_JEM t
        UNION ALL
        SELECT t.name, 'march' MONTH, t.march v_num
          FROM TEST_JEM t
        UNION ALL
        SELECT t.name, 'april' MONTH, t.april v_num
          FROM TEST_JEM t
        UNION ALL
        SELECT t.name, 'may' MONTH, t.may v_num FROM TEST_JEM t)
 ORDER BY NAME;      

🍃 1.2 insert all into … select

首先建立需要的表， test_row
create table test_row
(
NAME VARCHAR2(255),
MONTH VARCHAR2(8),
V_NUM NUMBER(18)
);

SQL> desc test_row      

然後執行下邊的 sql 語句：
注意：查詢test_jem 的表進行插入

insert all
into test_row(NAME,month,v_num) values(name, 'may', may)
into test_row(NAME,month,v_num) values(name, 'april', april)
into test_row(NAME,month,v_num) values(name, 'february', february)
into test_row(NAME,month,v_num) values(name, 'march', march)
into test_row(NAME,month,v_num) values(name, 'january', january)
select t.name,t.january,t.february,t.march,t.april,t.may from test_jem t;
commit;      

select * from test_row;

🍃 1.3 MODEL

CREATE TABLE t_col_row(
ID INT,
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10));
INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');
INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);
INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');
INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');
INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);
INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');
INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);
COMMIT;      

SELECT * FROM t_col_row;

SELECT id,
cn,
cv
FROM t_col_row
MODEL RETURN
UPDATED ROWS PARTITION BY(ID)
DIMENSION BY(0 AS n)
MEASURES('xx' AS cn, 'yyy' AS cv, c1, c2, c3)
RULES UPSERT ALL(cn[1] = 'c1', cn[2] = 'c2', cn[3] = 'c3', cv[1] = c1[0],
cv[2] = c2[0], cv[3] = c3[0])
ORDER BY ID,cn;      

🍁 二、行轉列

行轉列就是将行資料内容作為列名

CREATE TABLE t_row_col AS
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;
SELECT * FROM t_row_col ORDER BY 1,2;      

🍃 2.1 max+decode

SELECT id,
       MAX(decode(cn, 'c1', cv, NULL)) AS c1,
       MAX(decode(cn, 'c2', cv, NULL)) AS c2,
       MAX(decode(cn, 'c3', cv, NULL)) AS c3
  FROM t_row_col
 GROUP BY id
 ORDER BY 1;      

SELECT t.name,
MAX(decode(t.month, 'may', t.v_num)) AS may,
MAX(decode(t.month, 'april', t.v_num)) AS april,
MAX(decode(t.month, 'february', t.v_num)) AS february,
MAX(decode(t.month, 'march', t.v_num)) AS march,
MAX(decode(t.month, 'january', t.v_num)) AS january
FROM test_row t
GROUP BY t.name;      

如果要實作對各個不同的區間進行統計，則：

SELECT * FROM test_row t ORDER BY t.name, t.month;

SELECT t.name,
       CASE
         WHEN t.v_num < 100 THEN
          '0-100'
         WHEN t.v_num >= 100 AND t.v_num < 200 THEN
          '100-200'
         WHEN t.v_num >= 200 AND t.v_num < 300 THEN
          '200-300'
         WHEN t.v_num >= 300 AND t.v_num < 400 THEN
          '300-400'
       END AS grade,
       COUNT(t.v_num) count_num
  FROM test_row t
 GROUP BY t.name,
          CASE
            WHEN t.v_num < 100 THEN
             '0-100'
            WHEN t.v_num >= 100 AND t.v_num < 200 THEN
             '100-200'
            WHEN t.v_num >= 200 AND t.v_num < 300 THEN
             '200-300'
            WHEN t.v_num >= 300 AND t.v_num < 400 THEN
             '300-400'
          END;      

🍁 三、多列轉換成字元串

CREATE TABLE t_col_str AS
SELECT * FROM t_col_row;

這個比較簡單，用|| 或 concat 函數可以實作：
SELECT concat('a','b') FROM dual;


SELECT ID,
c1 || ',' || c2 || ',' || c3 AS c123
FROM t_col_str;      

🍁 四、多行轉換成字元串

CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10)
);
INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(1,'b');
INSERT INTO t_row_str VALUES(1,'c');
INSERT INTO t_row_str VALUES(2,'a');
INSERT INTO t_row_str VALUES(2,'d');
INSERT INTO t_row_str VALUES(2,'e');
INSERT INTO t_row_str VALUES(3,'c');
COMMIT;
SELECT * FROM t_row_str;      

4.1 ROW_NUMBER + LEAD

SELECT id, str
  FROM (SELECT id,
               row_number() over(PARTITION BY id ORDER BY col) AS rn,
               col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) ||
                lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||
                 lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str
          FROM t_row_str)
 WHERE rn = 1
 ORDER BY 1;