table 行转列的sql详解

一、要求

1 创建数据表

CREATE TABLE [dbo].[StuScore](

[stuid] [int] NOT NULL,

[subject] [nvarchar](30) NULL,

[score] [decimal](5, 1) NULL

)

2 插入测试数据

stuid subject score

3 chinese 76.0

3 math 73.0

4 chinese 82.0

5 chinese 66.0

5 math 93.0

6 chinese 67.0

7 math 83.0

8 chinese 77.0

8 math 84.0

3 行转列后的结果

stuid chinese math

3 76.0 73.0

4 82.0 0.0

5 66.0 93.0

6 67.0 0.0

7 0.0 83.0

8 77.0 84.0

二 、分析

1 行转列，一个重点就是怎么样知道有多少列，怎么样创建这些列？我们可以先把这个问题搁置，而假设这些列是已知的。 例如示例数据中，可以先假设subject的数据[chinese,math]是已知的，这样问题就简化了许多

2 当已知了chinese,math后，我们至少要先得到转换后的tabel结构

如下；

select stuid, 0 as chinese, 0 as math from dbo.StuScore

结果如下

stuid chinese math

3 0 0

3 0 0

4 0 0

5 0 0

5 0 0

6 0 0

7 0 0

8 0 0

8 0 0

3 接着就需要往这个数据集中去填充chinese, math的数据

select stuid,

case subject when 'chinese' then score else 0 end as chinese,

case subject when 'math' then score else 0 end as math

from dbo.StuScore

结果如下：

stuid chinese math

3 76.0 0.0

3 0.0 73.0

4 82.0 0.0

5 66.0 0.0

5 0.0 93.0

6 67.0 0.0

7 0.0 83.0

8 77.0 0.0

8 0.0 84.0

4 细心的读者会发现步骤3中的结果与我们想要的已经非常接近了，只需再做一个sum()处理，就OK了

select stuid,

sum(case subject when 'chinese' then score else 0 end ) as chinese,

sum(case subject when 'math' then score else 0 end ) as math

from dbo.StuScore group by stuid

得到的正是我们想要的结果

stuid chinese math

3 76.0 73.0

4 82.0 0.0

5 66.0 93.0

6 67.0 0.0

7 0.0 83.0

8 77.0 84.0

是不是现在就已经完成了呢？答案是否定的。前面我们已经说过，是为了简化问题，在假设已经知道了subject数据的情况下，这么处理的，实际上subject的数据是可变的，未知的，接下来就是要解决这个问题了

5 要获取subject的数据其实很简单

select distinct subject from dbo.StuScore

获取以后怎样得到case subject when 'chinese' then score else 0 end 这种语句？

可以根据subject的值去动态的组sql语句

看下面的一段代码

declare @sql varchar(2000)

set @sql=''

select @sql =@sql+ ',case subject when '''+subject+''' then 1 else 0 end as ' + subject

from (select distinct subject from dbo.StuScore) as sub

print @sql

message打印的信息如下：

,case subject when 'chinese' then 1 else 0 end as chinese,case subject when 'math' then 1 else 0 end as math

6 最后我们就需要将前面步骤综合起来，得到最终的sql

declare @sql varchar(2000)

set @sql='select stuid'

select @sql =@sql+ ',sum(case subject when '''+subject+''' then score else 0 end) as ' + subject

from (select distinct subject from dbo.StuScore) as sub

set @sql=@sql + ' from dbo.StuScore group by stuid'

exec(@sql)

stuid chinese math

3 76.0 73.0

4 82.0 0.0

5 66.0 93.0

6 67.0 0.0

7 0.0 83.0

8 77.0 84.0

至此，整个分析过程和结果就都出来了。

初试写文章， 多包涵，指正。