如何用指定时间段为动态字段生产交叉报表

Question

create table #t(
考点       varchar(10),
考场       varchar(10),
参考人数   int,
开考时间年 varchar(4),
月         varchar(2),
日         varchar(2),
时         varchar(2),
分         varchar(2))
insert into #t select '01','01',50,'2005','5','20','8' ,'30'
insert into #t select '01','02',51,'2005','5','21','12','30'
insert into #t select '02','01',52,'2005','5','20','15','30'
insert into #t select '02','01',53,'2005','5','22','19','30'
declare @s varchar(8000)
set @s = ''select @s = @s + ',['+开考时间年+'-'+月+'-'+日+'上午]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''08:30'' and ''11:30'' then 参考人数 else 0 end)'
               + ',['+开考时间年+'-'+月+'-'+日+'中午]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''12:30'' and ''14:30'' then 参考人数 else 0 end)'
               + ',['+开考时间年+'-'+月+'-'+日+'下午]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''15:30'' and ''17:30'' then 参考人数 else 0 end)'
               + ',['+开考时间年+'-'+月+'-'+日+'晚上]=SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''19:30'' and ''21:30'' then 参考人数 else 0 end)'
from
    (select distinct 开考时间年,月,日 from #t) t
set @s = 'select 考点'+@s+' from #t group by 考点'exec(@s)
--结果自己看

Answer 1

注意：如果--开考时间年、月、日、时、分以数字类型字段保存，则需要在引用时转为varchar()类型

Answer 2

呵呵，结果好像还是不对，SUM(case when right(''0''+时,2)+'':''+right(''0''+分,2) between ''08:30'' and ''11:30'' then 参考人数 else 0 end)会把表中所有时间段在8：30到11：30的参赛考人数都统计进来
以上得到的结果如下：
01  50 51 0  0  50  51 0  0  50  51  0  0
02  0 0  52 53  0   0 52 53 0  0  52 53显然不对

Answer 3

是有问题，忘记把年月日的条件加上了，再试：
--------------------------------------------------------------------------
create table #t(
考点       varchar(10),
考场       varchar(10),
参考人数   int,
开考时间年 varchar(4),
月         varchar(2),
日         varchar(2),
时         varchar(2),
分         varchar(2))
insert into #t select '01','01',50,'2005','5','20','8' ,'30'
insert into #t select '01','02',51,'2005','5','21','12','30'
insert into #t select '02','01',52,'2005','5','20','15','30'
insert into #t select '02','01',53,'2005','5','22','19','30'declare @s varchar(8000)
set @s = ''select @s = @s + ',['+开考时间年+'-'+月+'-'+日+'上午]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''08:30'' and ''11:30'' then 参考人数 else 0 end)'
               + ',['+开考时间年+'-'+月+'-'+日+'中午]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''12:30'' and ''14:30'' then 参考人数 else 0 end)'
               + ',['+开考时间年+'-'+月+'-'+日+'下午]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''15:30'' and ''17:30'' then 参考人数 else 0 end)'
               + ',['+开考时间年+'-'+月+'-'+日+'晚上]=SUM(case when 开考时间年='''+开考时间年+''' and 月='''+月+''' and 日='''+日+''' and right(''0''+时,2)+'':''+right(''0''+分,2) between ''19:30'' and ''21:30'' then 参考人数 else 0 end)'
from
    (select distinct 开考时间年,月,日 from #t) t
set @s = 'select 考点'+@s+' from #t group by 考点'
print @s
exec(@s)

Answer 4

libin_ftsafe(子陌红尘) 
速度迅速啊

Answer 5

--建立测试环境
Create Table TEST(
考点       varchar(10),
考场       varchar(10),
参考人数   int,
开考时间年 varchar(4),
月         varchar(2),
日         varchar(2),
时         varchar(2),
分         varchar(2))
--插入数据
Insert Into TEST Select '01','01',50,'2005','5','20','8' ,'30'
Insert Into TEST Select '01','02',51,'2005','5','21','12','30'
Insert Into TEST Select '02','01',52,'2005','5','20','15','30'
Insert Into TEST Select '02','01',53,'2005','5','22','19','30'
GO
--测试
Declare @S Nvarchar(4000)
Set @S=N'Select 考点'
Select @S=@S+N',SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''8''  And 时<''12'' Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'上午],
SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''12'' And 时<''15'' Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'中午],
SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''15'' And 时<''19'' Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'下午],
SUM(Case When 开考时间年='+ 开考时间年 +N' And 月='+ 月 +N' And 日='+ 日 +N' And 时>=''19''             Then 参考人数 Else 0 End) As ['+开考时间年+'-'+月+'-'+日+N'晚上]'
from (Select Distinct 开考时间年,月,日 from TEST ) A
Select @S=@S+ N' from TEST Group By 考点 '
--Select @S
EXEC(@S)
--删除测试环境
Drop Table TEST
--结果
/*
考点 2005-5-20上午 2005-5-20中午 2005-5-20下午 2005-5-20晚上 2005-5-21上午 2005-5-21中午 2005-5-21下午 2005-5-21晚上 2005-5-22上午 2005-5-22中午 2005-5-22下午 2005-5-22晚上
01 0 0 0 50 0 51 0 0 0 0 0 0
02 0 0 52 0 0 0 0 0 0 0 0 53
*/

Answer 6

呵呵，谢谢大家，特别是libin_ftsafe(子陌红尘)