数据库行转列的sql语句

问题描述

假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82

现在 想写 sql 语句     查询后结果 为    
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82       该怎么实现 ？

研究意义

        这是个并不复杂的问题，但却是数据库中行转列的一个典型例子，只要把这个抽象出来的具有普遍意义的问题研究透彻，其他类似的复杂问题迎刃而解。

问题分析

       首先介绍下行转列的概念，也许书上并没有这个概念，行转列说的是这样一类问题：有时候为了数据库表的设计满足用户的动态要求（比如添加字段），我们采用定义字段名表，然后定义一个字段值的表，这样就达到了用静态来表达动态，换句话说就是把数据库表中本来应该是横向的延伸转化为纵向的延伸，再换句话说就是把数据库表中本来应该是字段的增加转化为记录条数的增加。然而，在这样设计下，固然灵活，确带来了统计分析的麻烦，因为统计分析时，应该是以直观的形式进行表现。换言之，统计分析时，我们又应该显示为字段更多的那种。如果同时做到了数据存储时列的增加转化为行的增加，数据提取时又可得到列增加了的数据，数据库表的这种设计就对用户透明了。

        本文前面提出的这个问题就是一个典型的在数据提取时要把以行增加形式的数据转化为以列增加形式的数据。为什么这样说呢？我们注意subject字段，subject里的内容在数据库存储时是以不同数据行的形式，换言之，是以行增加的形式，而输出时，这里面的内容我们要变成字段名了。

        衡量这个问题解决好坏我们有几个标准：1.当数据正好就是上面这个样子时，解决办法能否得到正确的解；2.如果增加科目了科目的种类，解决方法是否仍然能行得通；3.如果有些人的某们课程的成绩还没有下来，换言之，数据库中不是每个人每门课的成绩都可以找到，数据库缺少某个人某门课的成绩的记录。在这种情况下程序还能否得到合理的结果。

试验环境

       本试验使用MS SQL Server 2005环境测试。

试验过程

       1.建立数据表，录入数据

        CREATE TABLE [dbo].[CJ](
[name] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[result] [int] NULL,
CONSTRAINT [PK_CJ] PRIMARY KEY CLUSTERED 
(
[name] ASC,
[subject] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

     通过可视化界面或者用insert语句录入数据

     2.第一个最直接，最简单的做法

         select distinct c.[name] as 姓名,
(select result from CJ where [name] = c.[name] and subject = '语文' )as 语文,
(select result from CJ where [name] = c.[name] and subject = '数学' )as 数学,
(select result from CJ where [name] = c.[name] and subject = '物理' )as 物理
from CJ c

          主要思想就是把任务分成两步，第一步：把第一列生成出来。第二步：根据第一列每行的姓名取值，查询该同学的各科成绩join到第一步生成的只有一列表。distinct不能省略。

          该方法能够完成该任务，但只能满足前文所述的评价标准1和标准3。当科目增多或者实际科目没有这么多时统计的结果就不那么完美了。换言之，这种方法是静态的，将科目在sql语句里写死了。另外中间的几个sql语句查询效率似乎并不那么高，还需要扫描整个表，实际上应该只需要在一个学生对应的几条记录里找就可以了。

       3.较好的办法

      先不管标准2，想想能不能解决那个扫描的效率问题。于是得到了下面的办法。

      select   [name] as 姓名,
sum(case when subject='语文' then result end) as 语文,
sum(case when subject='数学' then result end) as 数学,
sum(case when subject='物理' then result end) as 物理
from CJ group by [name]

      该办法大致思想类似前一种。最大的改进是用了group by，由于用了group by后字段名除了group by的那个其他不能直接用，加了个集函数，实际上这个Sum只会加一项，因为这个表的主键是name + subject。用了group by就会解决扫描的效率问题，因为sum是计算的每个分组之类的。本方法的技巧之处在于case when的使用。

       这个办法还是不能满足标准2。

      4.较完美的办法

     现在就是怎么解决subject“由死到活”的问题。想到了一种办法如下：

    declare @s nvarchar(1000)
select @s = 'select [name] as 姓名'
select @s = @s + ',sum(case when subject=''' + cast(subject as varchar) + ''' then result end) as ' + subject from CJ group by subject
select @s = @s + ' from CJ group by [name]'
exec(@s)

     其实思想是基于前面那种办法的，关键的地方就是通过动态生成sql语句，然后执行之。

    在@s的第一次累加中的代码中一句from CJ group by subject很是有技巧性，可见简单的select * from table t where .. 也是这么变化无穷，不得不佩服sql或者说关系型数据库的智慧。

本人收获

    a.认真的分析一个简单的问题的来龙去脉是很有意义的事情，浮躁的学风会让你花费大量的时间结果一无所获。

    b.解决一个问题要有清晰的思路，在一时不知道完美的答案时，可试图一步一步优化，向完美的方向靠近。

    c.要善于分析问题的症结所在，即抓住问题的本质。

写到最后

    这个问题暂时就说到这里，之所以把文章写出来是基于两个目的，首先，作为学习心得，不敢独享，希望更多的人能从中得到启发。其次，简单的问题也包含很多高深的知识，希望更多的高手能加入探讨，分析本文的不当之处，并给出更好的办法，或者提供更多的类似的例子，本文希望起到抛砖引玉的作用。