如何用SQL语句实现行列转换

如何用SQL语句实现行列转换

行列转换是数据库系统中经常遇到的一个需求，在数据库设计时，为了适合数据的累积存储，往往采用直接记录的方式，而在展示数据时，则希望整理所有记录并且转置显示。图9.1展示了行列转换的功能。

图9.1  行列转换的需求

分析这个需求，可以发现希望做的是找出具有相同部门的记录，并根据其材料的值累加数量。如果手动来写的话，最终希望得到的是下面这样的SQL语句：

select 部门, sum(case 材料 when '材料1' then 数量 else 0 end) [材料1], sum(case 材料 when '材料2' then 数量 else 0 end) [材料2], sum(case 材料 when '材料3' then 数量 else 0 end) [材料3] from 部门耗材 group by 部门

这是一个非常简单的查询语句，并且执行结果恰好就是希望得到的结果，但问题是，如何得知原表中究竟包含几种材料呢？显然，根据上述的SQL语句，得到的结果永远只能统计3种材料的消耗，这时候就需要动态地根据实际材料数目来得到查询语句。代码9-1实现了一个动态行列转换。

代码9-1  动态行列转换： Transfer.sql

 

--申明一个字符串变量，以供动态拼装 declare @sql varchar(8000) --拼装SQL命令 set @sql = 'select Department' --动态地获得材料，为每个材料构建一个列 select @sql = @sql + ',sum(case Item when '''+Item+''' then Number else 0 end) ['+Item+']' from (select distinct Item from DepartCost) as a --最终加上选择源和GROUP BY语句 select @sql = @sql+' from DepartCost group by Department' --执行SQL命令 exec(@sql)

 

为了书写方便，表名和列名都没有采用中文名字。建议读者在进行数据库设计时，尽量避免直接使用汉字，可以采用拼音或者缩写的方式来替代。

下面是这个SQL命令的执行结果：

Department	Item1	Item2	Item3
F1	3	1	2
F2	0	2	1
F3	1	0	1

这样的解决方案仍然有不少缺陷。主要有两点：第一是动态SQL命令执行效率往往不高，因为动态拼装的原因，导致数据库管理系统无法对这样的命令进行优化；第二是这样的SQL命令必须先确定其长度限制，而动态SQL命令的长度往往根据实际表的内容而改变，所以这个命令无法保证100%能够运行。

 

答案

 

行列转换的SQL命令通常需要依靠动态的SQL语句，具体的实现方法请参考本节的问题分析。