SQL行列转换

最新推荐文章于 2024-01-29 11:58:22 发布

LCLDeer

最新推荐文章于 2024-01-29 11:58:22 发布

阅读量422

点赞数 7

文章标签： sql 数据库

本文链接：https://blog.csdn.net/feiyuxc/article/details/134927829

版权

本文介绍了如何在SQL中实现行列转换，包括列转行和行转列两种操作，通过unionall、trans_cols、explode和case/when/collect_list等函数进行示例演示，以及如何准备测试数据并进行实际操作。

摘要由CSDN通过智能技术生成

一、行列转换形式理解

据了解，大家对行列转换的说法不统一，本文假设行列转换是以下形式
列转行
列转行：转换前数据是一行四列，转换后是三行三列，列少了，行多了。
转换前：

姓名	语文	数学	英语
张三	100	99	98

转换后：

姓名	科目	分数
张三	语文	100
张三	数学	99
张三	英语	98

行转列
行转列：转换前数据是三行三列，转换后是一行四列，行少了，列多了。
转换前：

姓名	科目	分数
张三	语文	100
张三	数学	99
张三	英语	98

转换后：

姓名	语文	数学	英语
张三	100	99	98

二、测试数据准备

CREATE TABLE test_h
(
    name STRING COMMENT '姓名'
    ,yw STRING COMMENT '语文'
    ,sx STRING COMMENT '数学'
    ,yy STRING COMMENT '英语'
)
;
INSERT INTO test_h 
VALUES ('张三','100','99','98') 
,('李四','99','98','97') ;
--------------------
create table test_2
(
    name STRING COMMENT '姓名'
    ,kemu string comment '科目'
    ,fenshu string COMMENT '分数'
);
INSERT into test_2
values('张三','英语','98')
,('李四','英语','97')
,('张三','数学','99')
,('李四','数学','98')
,('张三','语文','100')
,('李四','语文','99')
;

三、列转行

转换前：

name yw sx yy
张三 100 99 98
李四 99 98 97

转换后：

name kemu fenshu
张三英语 98
李四英语 97
张三数学 99
李四数学 98
张三语文 100
李四语文 99

3.1方式一
使用union all 将多次查询结果拼接一起.
union all 将select结果合并到一起(不去重）

SELECT name 
, '语文' as kemu
,yw as fenshu
from test_1
union all 
SELECT name 
,'数学' as kemu
,sx
from test_1
union all 
SELECT name 
,'英语' as kemu
,yy
from test_1
;

3.2方式二
使用高阶函数trans_cols对数据进行行列转换，split切分字段作为具体列数据。

SELECT  name
        ,split(fenshu,',')[0] as kemu
        ,split(fenshu,',')[1] as fenshu
FROM    (
            SELECT  trans_cols(1,name, yw, sx, yy) AS (id,name,fenshu)
            FROM    (
                        SELECT  name
                                ,'语文,'||yw AS yw
                                ,'数学,'||sx AS sx
                                ,'英语,'||yy AS yy
                        FROM    test_1
                    ) temp
        ) tmp
;

3.3方式三
将各科分数合并为一个字段，切分后，使用高阶函数explode将数据拆分成多行

SELECT  tmp.name
        ,split(b.kemu_fenshu,'_')[0] kemu
        ,split(b.kemu_fenshu,'_')[1] fenshu
FROM    (
            SELECT  name
                    ,(
                        '语文_'||yw||','||'数学_'||sx||','||'英语_'||yy
                    ) AS fenshu
            FROM    test_1
        ) tmp
LATERAL VIEW explode(split(fenshu,',')) b AS kemu_fenshu
;

四、行转列

转换前：

name kemu fenshu
张三英语 98
李四英语 97
张三数学 99
李四数学 98
张三语文 100
李四语文 99

转换后：

name yw sx yy
张三 100 99 98
李四 99 98 97

4.1方式一
使用case when 将数据按科目写成多列

SELECT name 
,max(case when kemu = '语文' then fenshu else -1 end) as 语文
,max(case when kemu = '数学' then fenshu else -1 end) as 数学
,max(case when kemu = '英语' then fenshu else -1 end) as 英语
from test_2
group by name 
;

4.2方式二
使用收集拼接函数将数据查询成多列。
注意：用在此处有点牵强，需要收集的字段默认顺序是，语文-》数学-》英语。此处更适用于无附加字段的情况。

SELECT name 
, split(concat_ws(',',collect_list(fenshu)),',')[0] as 语文
, split(concat_ws(',',collect_list(fenshu)),',')[1] as 数学
, split(concat_ws(',',collect_list(fenshu)),',')[2] as 英语
FROM    test_2
GROUP BY name
;