Sql数据库怎么实现行转列的sql语句

详细介绍sql
SqlServer数据库怎么实现行转列的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]