数据库行列转换

在进行报表开发时，或同一个用户的多条数据，查看起来比较费劲，经常会遇到行列转换操作。在查阅别人博客时也会遇到大大小小的坑，故在此总结一下几种常用数据库的行列转换的可行方法。

需求

首先说明一下我们的诉求，行列转换分为行转列，和列转行。建立一个测试表 stu_info

CREATE TABLE `stu_info` (
  `id` varchar(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  `hobby` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

然后插入一些测试数据，如下

INSERT INTO `stu_info`(`id`, `stu_name`, `course`, `score`, `hobby`) VALUES ('1', '张三', '语文', 78, '游泳,玩游戏,吃火锅');
INSERT INTO `stu_info`(`id`, `stu_name`, `course`, `score`, `hobby`) VALUES ('2', '张三', '数学', 88, NULL);
INSERT INTO `stu_info`(`id`, `stu_name`, `course`, `score`, `hobby`) VALUES ('3', '张三', '英语', 94, NULL);
INSERT INTO `stu_info`(`id`, `stu_name`, `course`, `score`, `hobby`) VALUES ('4', '李四', '语文', 76, '打球,看电影');
INSERT INTO `stu_info`(`id`, `stu_name`, `course`, `score`, `hobby`) VALUES ('5', '李四', '数学', 78, NULL);
INSERT INTO `stu_info`(`id`, `stu_name`, `course`, `score`, `hobby`) VALUES ('6', '李四', '英语', 88, NULL);

行转列

把多行的数据用分隔符转到一列中显示，如下图所示，上面是转换之前的数据，下面是转换之后的结果

列转换

把一列的数据用分隔符拆分为多行中显示，如下图所示

Mysql

行转列

mysql行转列可使用GROUP_CONCAT函数

-- 行转列
SELECT
    stu_name,
    GROUP_CONCAT(course, ":", score) AS score_info
FROM
    stu_info
GROUP BY
    stu_name;

列转行

列转行有几种方式，可以通过if判断，把多列转成一行显示，sql如下

-- 列转行
select stu_name,
    sum(if(course='语文',score,0)) as chinese,
    sum(if(course='数学',score,0)) as math,
    sum(if(course='英语',score,0)) as english,
    sum(score) as score
from stu_info group by stu_name;

有时我们需要把一个字段拆分为多行显示，sql如下

SELECT
    a.id,
    a.stu_name,
    substring_index(
        substring_index(
            a.hobby,
            ',',
            b.help_topic_id + 1
        ),
        ',' ,- 1
    ) AS hobbys
FROM
    stu_info a
JOIN mysql.help_topic b ON b.help_topic_id < (
    length(a.hobby) - length(
        REPLACE (a.hobby, ',', '')
    ) + 1
)

Oracle

行转列

oracle提供了关键函数pivot，可快速的实现行转列，用法如下

--行转列
select *
  from stu_info pivot(
    max(score) for course in (    --shangPin 即要转成列的字段
        '语文' as chinese,                 --max(salesNum) 此处必须为聚合函数，
        '数学' as math,                 --in () 对要转成列的每一个值指定一个列名
        '英语' as english
    )
  )
 where 1 = 1;                          --这里可以写查询条件，没有可以直接不要where

列转行

unpivot是把多列转换到一行显示，如下图

sql中使用unpivot函数实现

select student,科目,成绩 from tmp_2 unpivot (成绩 for 科目 in (语文, 数学, 英语, 物理));

PostgreSQL

在PgSql中提供了两个函数可以方便快捷的实现行列转换，分别是regexp_split_to_table，string_agg 函数

行转列

string_agg函数会把同一个人的多行数据按指定的分割符拼接在一起作为一个字段显示

select stu_name,string_agg(score,',') as scores from stu_info group by stu_name

列转行

regexp_split_to_table函数会把字段按指定的分隔符切割并把切割开的结果作为多行显示

select stu_name,regexp_split_to_table(score,',') as scores from stu_info