oracle中的行列转置

前言

以前接触过通用SQL中的行列转置，行转列使用聚合函数sum | max 然后结合case when then else end 语句就可以实现，而列转行使用union或union all来实现。这样做其实有点麻烦，而在oracle数据中，专门提供了这样的函数来实现行列转置功能。

通用SQL行列转置

1、行转列

先准备好数据表stu_score，表中数据如下：

现在想要实现以下的效果：

通用的SQL如下：

SELECT NAME
	姓名,
	max( CASE SUBJECT WHEN '语文' THEN score ELSE 0 END ) 语文,
	max( CASE SUBJECT WHEN '数学' THEN score ELSE 0 END ) 数学,
	max( CASE SUBJECT WHEN '英语' THEN score ELSE 0 END ) 英语 
FROM
	stu_score 
GROUP BY
	NAME;

2、列转行

事先准备好e_sal表，表数据如下：

现在想实现以下效果：

通用的SQL如下：

SELECT NAME
	姓名,
	'1月' AS 月份,
	M_1 AS 工资 
FROM
	e_sal UNION
SELECT NAME
	姓名,
	'2月' AS 月份,
	M_2 AS 工资 
FROM
	e_sal UNION
SELECT NAME
	姓名,
	'3月' AS 月份,
	M_3 AS 工资 
FROM
	e_sal UNION
SELECT NAME
	姓名,
	'4月' AS 月份,
	M_4 AS 工资 
FROM
	e_sal;

oracle中的行列转置

同样可以使用通用SQL来实现行列转置，但是Oracle提供了专门的函数来处理行列转置，也就是使用pivot函数和unpivot函数，下面进行介绍。

1、pivot行转列

oracle数据库中提供pivot函数来实现行转列的功能，同样实现上面行转列的效果，SQL也可以这样写：

SELECT NAME AS
	姓名,
	"'语文'",
	"'数学'",
	"'英语'" 
FROM
	stu_score pivot ( max( score ) FOR SUBJECT IN ( '语文', '数学', '英语' ) );

查询结果如下：

2、unpivot列转行

oracle中提供unpivot函数来实现列转行功能，实现同样的效果，SQL也可以这样写：

SELECT NAME AS
	姓名,
	月份,工资 
FROM
	e_sal unpivot ( 工资 FOR 月份 IN ( M_1, M_2, M_3, M_4 ) );

查询结果如下：

小结

虽然Oracle提供了专门的函数来实现行列转置功能，但是有局限性，这里就不叙述了，建议使用通用SQL实现行列转置，因为在MySQL和Oracle数据库中都是可以用的，而后者只能在Oracle中使用，所以考虑通用性，建议使用通用SQL。

一起学习，一起进步，每天只要进步一点点，时间久了，就是质的飞跃。