table行转列的sql详解
table行转列的sql详解 tabele行转列的资料，网上搜一下很多。大家照着网上copy就可以实现自己想要的功能。但是大家在实现功能后是否想过行转列为什么要这样写？下面就以一个实例来分析， 希望对初学者有所帮助。 一、要求 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 @sqlmessage打印的信息如下： ,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 至此，整个分析过程和结果就都出来了。 初试写文章， 多包涵，指正。