本文基于示例为您介绍如何使用SQL实现行转列、列转行需求。

背景信息

行转列与列转行的示意图如下。 行转列与列转行
  • 行转列

    将多行数据转换成一行显示，或将一列数据转换成多列显示。

  • 列转行

    将一行数据转换成多行显示，或将多列数据转换成一列显示。

示例数据

为便于理解后续代码示例，本文为您提供源数据，并基于源数据提供相关转换示例。

  • 创建用于实现 行转列 的源表并插入数据，命令示例如下。 
    create table rowtocolumn (name string, subject string, result bigint);
insert into table rowtocolumn values 
('张三' , '语文' , 74),
('张三' , '数学' , 83),
('张三' , '物理' , 93),
('李四' , '语文' , 74),
('李四' , '数学' , 84),
('李四' , '物理' , 94);
    查询表rowtocolumn中的数据，命令示例如下： 
    select * from rowtocolumn;
--返回结果。
+------------+------------+------------+
| name       | subject    | result     |
+------------+------------+------------+
| 张三        | 语文        | 74         |
| 张三        | 数学        | 83         |
| 张三        | 物理        | 93         |
| 李四        | 语文        | 74         |
| 李四        | 数学        | 84         |
| 李四        | 物理        | 94         |
| 张三        | 语文        | 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);
    查询表columntorow中的数据，命令示例如下： 
    select * from columntorow;
--返回结果。
+------------+------------+-------------+------------+
| name       | chinese    | mathematics | physics    |
+------------+------------+-------------+------------+
| 张三        | 74         | 83          | 93         |
| 李四        | 74         | 84          | 94         |
| 张三        | 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        |
+--------+------------+------------+------------+
说明 在实际业务开发过程中，如果您遇到行转列需求，还可以通过 Lateral View 、 EXPLODE 函数、 INLINE 函数以及 TRANS_ARRAY 函数将单行数据转为多行数据。

列转行示例

您可以通过如下两种方法实现列转行：
  • 方法一：使用 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         |
+--------+--------+------------+