SQL行转列及列转行最佳实践
本文基于示例为您介绍如何使用SQL实现行转列、列转行需求。
背景信息
行转列与列转行的示意图如下。
- 行转列
将多行数据转换成一行显示，或将一列数据转换成多列显示。
- 列转行
将一行数据转换成多行显示，或将多列数据转换成一列显示。
示例数据
为便于理解后续代码示例，本文为您提供源数据，并基于源数据提供相关转换示例。
- 创建用于实现行转列的源表并插入数据，命令示例如下。
create table rowtocolumn (name string, subject string, result bigint); insert into table rowtocolumn values ('张三' , '语文' , 74), ('张三' , '数学' , 83), ('张三' , '物理' , 93), ('李四' , '语文' , 74), ('李四' , '数学' , 84), ('李四' , '物理' , 94); - 创建用于实现列转行的源表并插入数据，命令示例如下。
create table columntorow (name string, chinese bigint, mathematics bigint, physics bigint); insert into table columntorow values ('张三' , 74, 83, 93), ('李四' , 74, 84, 94);
行转列示例
您可以通过如下两种方法实现行转列：
- 方法一：使用
case when表达式，灵活提取各科目（subject）的值作为单独的列，命令示例如下。select name as 姓名, max(case subject when '语文' then result end) as 语文, max(case subject when '数学' then result end) as 数学, max(case subject when '物理' then result end) as 物理 from rowtocolumn group by name;返回结果如下。
+--------+------------+------------+------------+ | 姓名 | 语文 | 数学 | 物理 | +--------+------------+------------+------------+ | 张三 | 74 | 83 | 93 | | 李四 | 74 | 84 | 94 | +--------+------------+------------+------------+ - 方法二：借助MaxCompute提供的内建函数实现，先基于CONCAT和WM_CONCAT函数合并科目和成绩为一列，然后通过KEYVALUE函数解析科目（subject）的值作为单独的列。命令示例如下。
select name as 姓名, keyvalue(subject, '语文') as 语文, keyvalue(subject, '数学') as 数学, keyvalue(subject, '物理') as 物理 from( select name, wm_concat(';',concat(subject,':',result))as subject from rowtocolumn group by name);返回结果如下。
+--------+------------+------------+------------+ | 姓名 | 语文 | 数学 | 物理 | +--------+------------+------------+------------+ | 张三 | 74 | 83 | 93 | | 李四 | 74 | 84 | 94 | +--------+------------+------------+------------+
列转行示例
您可以通过如下两种方法实现列转行：
- 方法一：使用
union all，将各科目（chinese、mathematics、physics）整合为一列，命令示例如下。--解除order by必须带limit的限制，方便列转行SQL命令对结果按照姓名排序。 set odps.sql.validate.orderby.limit=false; --列转行SQL。 select name as 姓名, subject as 科目, result as 成绩 from( select name, '语文' as subject, chinese as result from columntorow union all select name, '数学' as subject, mathematics as result from columntorow union all select name, '物理' as subject, physics as result from columntorow) order by name;返回结果如下。
+--------+--------+------------+ | 姓名 | 科目 | 成绩 | +--------+--------+------------+ | 张三 | 语文 | 74 | | 张三 | 数学 | 83 | | 张三 | 物理 | 93 | | 李四 | 语文 | 74 | | 李四 | 数学 | 84 | | 李四 | 物理 | 94 | +--------+--------+------------+ - 方法二：借助MaxCompute提供的内建函数实现，先基于CONCAT函数拼接各科目和成绩，然后基于TRANS_ARRAY和SPLIT_PART函数逐层拆解科目和成绩作为单独的列。命令示例如下。
select name as 姓名, split_part(subject,':',1) as 科目, split_part(subject,':',2) as 成绩 from( select trans_array(1,';',name,subject) as (name,subject) from( select name, concat('语文',':',chinese,';','数学',':',mathematics,';','物理',':',physics) as subject from columntorow)tt)tx;返回结果如下。
+--------+--------+------------+ | 姓名 | 科目 | 成绩 | +--------+--------+------------+ | 张三 | 语文 | 74 | | 张三 | 数学 | 83 | | 张三 | 物理 | 93 | | 李四 | 语文 | 74 | | 李四 | 数学 | 84 | | 李四 | 物理 | 94 | +--------+--------+------------+