T-Sql行、列转换问题（SqlServer2008）
0
悬赏园豆：10
[已解决问题]
解决于 2012-02-06 11:24
源数据如图1
想用一条Sql语句查询出如下图2的结果
即根据根据Week列来统计每周的结果，生成生成的列名要用"周名字"+"-"+"天名字"(例如"Week01-Mon"表示第一周的星期一).
我现在已经写了一条T-sql如下
select Process,MouldNo,HoleCount,MachineNo,Type,sum(case Week when 'Week01' then Sun end) [Week01Sun],sum(case Week when 'Week02' then Sun end) [Week02Sun]
,sum(case Week when 'Week01' then Mon end) [Week01-Mon],sum(case Week when 'Week02' then Mon end) [Week02-Mon]
,sum(case Week when 'Week01' then Tue end) [Week01-Tue],sum(case Week when 'Week02' then Tue end) [Week02-Tue]
,sum(case Week when 'Week01' then Wed end) [Week01-Wed],sum(case Week when 'Week02' then Wed end) [Week02-Wed]
,sum(case Week when 'Week01' then Thu end) [Week01-Thu],sum(case Week when 'Week02' then Thu end) [Week02-Thu]
,sum(case Week when 'Week01' then Fir end) [Week01-Fir],sum(case Week when 'Week02' then Fir end) [Week02-Fir]
,sum(case Week when 'Week01' then Sta end) [Week01-Sta],sum(case Week when 'Week02' then Sta end) [Week02-Sta]
from V_Process group by Process,MouldNo,HoleCount,MachineNo,Type
能够实现上面的要求，但是这样有一个缺点，就是这个要根据查询的数据来改变SQL语句，比如如果要查询三周的数据的话就要添加新的语句，不知道有没有一个能够循环的SQl，只要输入参数就可以查询不同时间范围的数据。
新手没有都少分，希望各位给点提示，O(∩_∩)O谢谢！
最佳答案
0
找到方法了
go
declare @sql varchar(8000)
set @sql = 'select Process,MouldNo,HoleCount,MachineNo,Type'
select @sql = @sql + ',sum(case Week when '''+Week+''' then Sun end) ['+Week+'Sun'+']'
from (select distinct Week from V_Process) as a
select @sql = @sql + ',sum(case Week when '''+Week+''' then Mon end) ['+Week+'Mon'+']'
from (select distinct Week from V_Process) as a
select @sql = @sql+' from V_Process'
--select @sql = @sql+' where MouldNo in(''#1'',''#2'')'
select @sql = @sql+' group by Process,MouldNo,HoleCount,MachineNo,Type'
print(@sql)
exec(@sql)
用这个嵌套查询就可以实现了，谢谢大家。
不过应该还有更简洁的办法，我在研究研究。
