SQL技巧：行列转换

SQL技巧：行列转换

年中汇报的时候，用图一的形式给老板老王做了人员工资表，老板老王把我一顿剋，让我卷铺盖走人。于是苦思冥想的转换成了图二的形式，老板老王态度180度大反转，说要给我升职加薪，饼太大有点吃不下。

在数据处理过程中，我们有时需要将行数据转换为列，或者将列数据转换为行。这种转换方式在数据分析、报告和其他许多领域都非常常见和有用。在Oracle数据库中，我们常用的是case when end和union all。也可以使用一些内置函数来实现上述操作，具体包括PIVOT和UNPIVOT操作。接下来，本文将对行列转换进行赘述。

1.数据准备

with t_test as (
select '老昝' as c_name ,'一月' as c_month, 10000 as c_sal from dual
union all 
select '老昝' as c_name ,'二月' as c_month, 2000 as c_sal from dual
union all 
select '老昝' as c_name ,'三月' as c_month, 3000 as sal from dual
union all
select '老昝' as c_name ,'四月' as c_month, 1999 as sal from dual
union all 
select '老昝' as c_name ,'五月' as c_month, 999 as sal from dual
union all 
select '老昝' as c_name ,'六月' as c_month, 220 as sal from dual
union all 
select '大腾' as c_name ,'一月' as c_month, 20000 as sal from dual
union all
select '大腾' as c_name ,'二月' as c_month, 26784 as sal from dual
union all 
select '大腾' as c_name ,'三月' as c_month, 5000 as sal from dual
union all
select '大腾' as c_name ,'四月' as c_month, 34765 as sal from dual
union all 
select '大腾' as c_name ,'五月' as c_month, 10000 as sal from dual
union all 
select '大腾' as c_name ,'六月' as c_month, 29999 as sal from dual
)

1.1 行转列：CASE WHEN END

首先根据不同的条件获取不同的值，就可以把数据分为多列。

select 
       c_name as 员工
       ,case when c_month = '一月' then c_sal end as 一月工资
       ,case when c_month = '二月' then c_sal end as 二月工资
       ,case when c_month = '三月' then c_sal end as 三月工资
       ,case when c_month = '四月' then c_sal end as 四月工资
       ,case when c_month = '五月' then c_sal end as 五月工资
       ,case when c_month = '六月' then c_sal end as 六月工资
       ,c_sal as 上半年合计
from t_test

结果如下：

然后我们再根据员工进行汇总就得到了老板老王喜欢的数据

select 
       c_name as 员工
       ,sum(case when c_month = '一月' then c_sal end) as 一月工资
       ,sum(case when c_month = '二月' then c_sal end) as 二月工资
       ,sum(case when c_month = '三月' then c_sal end) as 三月工资
       ,sum(case when c_month = '四月' then c_sal end) as 四月工资
       ,sum(case when c_month = '五月' then c_sal end) as 五月工资
       ,sum(case when c_month = '六月' then c_sal end) as 六月工资
       ,sum(c_sal) as 上半年合计
from t_test
group by c_name

结果如下：可以清晰看到大腾和老昝两位员工上半年各月份工资情况，而且大腾工资远超老昝。

1.2 行转列：PIVOT

PIVOT操作是指把多行数据旋转成多列数据。

下面是基础的PIVOT语法：

SELECT ...
FROM ...
PIVOT (aggregate_function(column_to_aggregate)
       FOR column_to_pivot
       IN  (list_of_values))

下面是用于实现上述业务场景的SQL查询示例：

SELECT * FROM t_test
PIVOT (SUM(c_sal) as 工资 FOR c_month IN ('一月' as 一月, '二月'as 二月, '三月' as 三月, '四月' as 四月, '五月' as 五月, '六月' as 六月));

输出结果将以月份为列索引，工资。

关键点：

• PIVOT运算符将每个唯一值从for_clause指定的列生成新的列，并聚合在agg_expression中指定的值。
• 当存在多个PIVOT列时，需要使用括号将其围起来，并用逗号分隔。

2.数据准备

with t_test as (
select '老昝' as c_name ,10000 as 一月， 2000 as 二月 ,3000 as 三月 ,1999 as 四月,999 as 五月,220 as 六月 from dual
union all 
select '大腾' as c_name ,20000 as 一月， 26784 as 二月 ,5000 as 三月 ,34765 as 四月,10000 as 五月,29999 as 六月 from dual
)

2.1 列转行：UNION ALL

select c_name，一月 from t_test
union all 
select c_name，二月 from t_test
union all 
select c_name，三月 from t_test
union all 
select c_name，四月 from t_test
union all 
select c_name，五月 from t_test
union all 
select c_name，六月 from t_test

2.2 列转行：UNPIVOT

对于列数据转换为行的情况，我们使用UNPIVOT操作，也就是进行“逆透视”。例如，在上一步的结果中，我们可能想要返回到原始格式。

下面是基础的UNPIVOT语法：

SELECT ...
FROM ...
UNPIVOT (column_to_unpivot 
         FOR new_column 
         IN  (list_of_columns))

下面是用于实现上述业务场景的SQL查询示例：

SELECT * FROM t_test
UNPIVOT (工资 FOR c_month IN (一月,二月, 三月,四月,五月,六月))

输出结果将把月份的数据从各自的列“逆透视”为行，每人具有的数据行数将为指定月份。

关键点：

• UNPIVOT运算符将列转换为行。
• FOR语句后接收一个新的列名，该列将包含从原始列取得的值。
• IN列表中的列将被转化为行，它们的值会填充到新的列里。

3. 注意事项

在进行行列转换时需要注意以下几个点：

• 转换可能会导致数据产生重复，因此使用这两个操作需要谨慎处理可能出现的重复数据问题；
• 对于NULL值，既可以在PIVOT操作中被自动忽略，也可以在UNPIVOT操作中自动排除。如果你希望保留这些值，必须将它们替换为其他非空值。
• 在PIVOT中，要明确提供聚合函数如MAX、MIN、SUM等， 对于非数字类别，通常使用MAX或MIN做聚合；在使用UNPIVOT时，没有这种限制。

4. 结论

在数据处理中，行列转换无疑扮演了一个重要的角色。Oracle数据库所提供的PIVOT和UNPIVOT操作简单而强大，它们可以帮助我们轻松实现多样化的业务需求。虽然这些转换操作可能需要一点时间理解和应用，但是一旦掌握，就会发现其实是一个非常有用且可大大优化我们数据处理和分析工作的工具。

希望通过这篇公众号文章，你能对Oracle中的行列转换有更深入的理解和应用。如果还有任何疑问或困惑，欢迎随时留言交流。未来我们将继续分享更多关于数据库管理、数据处理和分析的知识，敬请期待！让我们一起成长，共享数据的力量！

5.参考文献

《Oracle查询优化改写》