星期的统计纵变横
2007-01-01 00:00:00.000101480.0NULLNULL.0
2007-01-02 00:00:00.00010148NULLNULL.0NULL
2007-01-03 00:00:00.000101488.03.0.0NULL
2007-01-04 00:00:00.000101488.03.0.0NULL
2007-01-05 00:00:00.000101488.03.0.0NULL
2007-01-06 00:00:00.000101488.0.0.0NULL
2007-01-07 00:00:00.00010148NULLNULL.0NULL
工号 周一1.1 周二1.2 周三1.3 周四1.4 周五1.5 周六1.6 周日1.7
10148 0 0 0 0 0 0 8 3 0 8 3 0 8 3 0 8 0 0 0 0 0
[解决办法]
--参考
create table T(col1 datetime, col2 varchar(20), col3 decimal(10,1), col4 decimal(10,1), col5 decimal(10,1), col6 decimal(10,1))
insert T select '2007-01-01 00:00:00.000 ', '10148 ',0,NULL,NULL,0
union all select '2007-01-02 00:00:00.000 ', '10148 ',NULL,NULL,0,NULL
union all select '2007-01-03 00:00:00.000 ', '10148 ',8.0,3.0,0,NULL
union all select '2007-01-04 00:00:00.000 ', '10148 ',8.0,3.0,0,NULL
union all select '2007-01-05 00:00:00.000 ', '10148 ',8.0,3.0,0,NULL
union all select '2007-01-06 00:00:00.000 ', '10148 ',8.0,0, 0,NULL
union all select '2007-01-07 00:00:00.000 ', '10148 ',NULL,NULL,0,NULL
select col2,
[周一]=sum(case when (datepart(weekday, col1)+@@datefirst-1)%7=1 then col3 else 0 end),
[周二]=sum(case when (datepart(weekday, col1)+@@datefirst-1)%7=2 then col3 else 0 end),
[周三]=sum(case when (datepart(weekday, col1)+@@datefirst-1)%7=3 then col3 else 0 end),
[周四]=sum(case when (datepart(weekday, col1)+@@datefirst-1)%7=4 then col3 else 0 end),
[周五]=sum(case when (datepart(weekday, col1)+@@datefirst-1)%7=5 then col3 else 0 end),
[周六]=sum(case when (datepart(weekday, col1)+@@datefirst-1)%7=6 then col3 else 0 end),
[周日]=sum(case when (datepart(weekday, col1)+@@datefirst-1)%7=0 then col3 else 0 end)
from T
group by col2
[解决办法]
就这有这几条记录吗？
你的帖子又让我想起超级难搞的横排问题
