首页新闻找找看学习计划

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谢谢!

Gamain的主页 Gamain | 菜鸟二级 | 园豆:357
提问于:2012-02-06 10:23
< >
分享
最佳答案
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)

用这个嵌套查询就可以实现了,谢谢大家。

不过应该还有更简洁的办法,我在研究研究。

Gamain | 菜鸟二级 |园豆:357 | 2012-02-06 11:24
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册