declare @t table ( Name varchar(4),Department varchar(4), Score numeric(3,2),year int,week int, StartTime datetime,EndTime DATETIME ) insert into @t select '张三','资讯',8.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all select '李四','信息',9.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all select '张三','资讯',7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all select '李四','信息',8.7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all select '张三','资讯',8.78,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all select '李四','信息',9.79,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all select '张三','资讯',7.78,2010,4,'2010-01-25 00:00:00.000','2010-01-24 00:00:00.000' union all select '李四','信息',8.79,2010,4,'2010-01-25 00:00:00.000','2010-01-31 00:00:00.000'select department as 部门,name as 姓名, sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0)) when 1 then score else 0 end) as [2010年第1周,(2010-01-04到2010-01-10)], sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0)) when 2 then score else 0 end) as [2010年第2周,(2010-01-11到2010-01-17)], sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0)) when 3 then score else 0 end) as [2010年第3周,(2010-01-18到2010-01-24)], sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0)) when 4 then score else 0 end) as [2010年第4周,(2010-01-25到2010-01-31)] from @t group by name,department /* 部门 姓名 2010年第1周,(2010-01-04到2010-01-1 2010年第2周,(2010-01-11到2010-01-1 2010年第3周,(2010-01-18到2010-01-2 2010年第4周,(2010-01-25到2010-01-3 ---- ---- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 信息 李四 9.00 8.70 9.79 8.79 资讯 张三 8.00 7.00 8.78 7.78 */
create table lstcyzj ( Name varchar(4),Department varchar(4), Score numeric(3,2),year int,week int, StartTime datetime,EndTime DATETIME ) insert into lstcyzj select '张三','资讯',8.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all select '李四','信息',9.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all select '张三','资讯',7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all select '李四','信息',8.7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all select '张三','资讯',8.78,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all select '李四','信息',9.79,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all select '张三','资讯',7.78,2010,4,'2010-01-25 00:00:00.000','2010-01-31 00:00:00.000' union all select '李四','信息',8.79,2010,4,'2010-01-25 00:00:00.000','2010-01-31 00:00:00.000' declare @sql varchar(8000) set @sql = 'select department as 部门,name as 姓名 ' select @sql = @sql + ' ,sum(case ceiling((datediff(d,''2010-01-01'',starttime)/7.0)) when ' + ltrim(id) + ' then score else 0 end) [' + col + ']' from (select distinct ceiling((datediff(d,'2010-01-01',starttime)/7.0)) as id, '2010年第'+ ltrim(ceiling((datediff(d,'2010-01-01',starttime)/7.0)))+ '周('+convert(varchar(10),StartTime,120)+'到'+convert(varchar(10),EndTime,120)+')' as col from lstcyzj ) as a set @sql = @sql + ' from lstcyzj group by name,department' exec(@sql)
declare @t table
(
Name varchar(4),Department varchar(4),
Score numeric(3,2),year int,week int,
StartTime datetime,EndTime DATETIME
)
insert into @t
select '张三','资讯',8.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all
select '李四','信息',9.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all
select '张三','资讯',7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all
select '李四','信息',8.7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all
select '张三','资讯',8.78,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all
select '李四','信息',9.79,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all
select '张三','资讯',7.78,2010,4,'2010-01-25 00:00:00.000','2010-01-24 00:00:00.000' union all
select '李四','信息',8.79,2010,4,'2010-01-25 00:00:00.000','2010-01-31 00:00:00.000'select department as 部门,name as 姓名,
sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0))
when 1 then score else 0 end) as [2010年第1周,(2010-01-04到2010-01-10)],
sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0))
when 2 then score else 0 end) as [2010年第2周,(2010-01-11到2010-01-17)],
sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0))
when 3 then score else 0 end) as [2010年第3周,(2010-01-18到2010-01-24)],
sum(case ceiling((datediff(d,'2010-01-01',starttime)/7.0))
when 4 then score else 0 end) as [2010年第4周,(2010-01-25到2010-01-31)]
from @t group by name,department
/*
部门 姓名 2010年第1周,(2010-01-04到2010-01-1 2010年第2周,(2010-01-11到2010-01-1 2010年第3周,(2010-01-18到2010-01-2 2010年第4周,(2010-01-25到2010-01-3
---- ---- ------------------------------ ------------------------------ ------------------------------ ------------------------------
信息 李四 9.00 8.70 9.79 8.79
资讯 张三 8.00 7.00 8.78 7.78
*/
create table lstcyzj
(
Name varchar(4),Department varchar(4),
Score numeric(3,2),year int,week int,
StartTime datetime,EndTime DATETIME
)
insert into lstcyzj
select '张三','资讯',8.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all
select '李四','信息',9.0,2010,1,'2010-01-04 00:00:00.000','2010-01-10 00:00:00.000' union all
select '张三','资讯',7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all
select '李四','信息',8.7,2010,2,'2010-01-11 00:00:00.000','2010-01-17 00:00:00.000' union all
select '张三','资讯',8.78,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all
select '李四','信息',9.79,2010,3,'2010-01-18 00:00:00.000','2010-01-24 00:00:00.000' union all
select '张三','资讯',7.78,2010,4,'2010-01-25 00:00:00.000','2010-01-31 00:00:00.000' union all
select '李四','信息',8.79,2010,4,'2010-01-25 00:00:00.000','2010-01-31 00:00:00.000'
declare @sql varchar(8000)
set @sql = 'select department as 部门,name as 姓名 '
select @sql = @sql +
' ,sum(case ceiling((datediff(d,''2010-01-01'',starttime)/7.0)) when ' + ltrim(id) + ' then score else 0 end) [' + col + ']'
from (select
distinct ceiling((datediff(d,'2010-01-01',starttime)/7.0)) as id, '2010年第'+
ltrim(ceiling((datediff(d,'2010-01-01',starttime)/7.0)))+
'周('+convert(varchar(10),StartTime,120)+'到'+convert(varchar(10),EndTime,120)+')' as col
from lstcyzj ) as a
set @sql = @sql + ' from lstcyzj group by name,department'
exec(@sql)