重温SQL行列互转，性能又双叒提升了

​「这是我参与2022首次更文挑战的第3天，活动详情查看：2022首次更文挑战

摘要：最近粉丝们提的问题都与行列转换有关系，我对行列转换的相关知识做了一个总结

🍁 1.列转行

简单的说就是将原表中的列名作为转换后的表的内容，这就是列转行

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

🍁 2.列转行

行转列就是将行数据内容作为列名

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;

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;

🍁 3.多列转换成字符串

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;

🍁 4.多行转换成字符串

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;

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;

相关推荐阅读： MySQL8.0新特性抢先看，性能又双叒提升了
MySQL武林秘籍，SQL学废必过考试
Linux7.6源码安装Mysql8
Oracle巡检脚本大全，服务器可直接部署
MySQL root密码忘记找回妙招
监控神器Zabbix，从部署到应用
Oracle监听日志清除

大家可以点赞、收藏、关注、评论我啦 、有数据库相关的问题随时联系我或交流哟~！