表字段及记录如下: ID T0 ReadTime
000000000001 479.12 2007-2-26 00:15:43
000000000001 479.12 2007-2-26 1:15:43
000000000001 479.12 2007-2-26 2:15:43
000000000001 479.12 2007-2-26 3:15:43
000000000001 479.12 2007-2-26 4:15:43
000000000001 479.12 2007-2-26 5:15:43
000000000001 479.12 2007-2-26 6:15:43
000000000001 479.12 2007-2-26 7:15:43
000000000001 479.12 2007-2-26 8:15:43
000000000001 479.12 2007-2-26 9:15:43
000000000001 479.12 2007-2-26 10:15:43
000000000001 479.12 2007-2-26 11:15:43
000000000001 479.12 2007-2-26 12:15:43
000000000001 479.12 2007-2-26 13:15:43
000000000001 479.12 2007-2-26 14:15:43
000000000001 479.12 2007-2-26 15:15:43
000000000001 479.12 2007-2-26 16:15:43
000000000001 479.12 2007-2-26 17:15:43
000000000001 479.12 2007-2-26 18:15:43
000000000001 479.12 2007-2-26 19:15:43
000000000001 479.12 2007-2-26 20:15:43
000000000001 479.12 2007-2-26 21:15:43
000000000001 479.12 2007-2-26 22:15:43
000000000001 479.12 2007-2-26 23:15:43
现在要将每天的数据分成24点来显示,比如2007-2-26 的数据(见上图),共有24条记录,把这24条记录取出来在一行分24个来显示,对应时间下面是这个时间点的值,如下:
0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12
000000000001 479.12 2007-2-26 00:15:43
000000000001 479.12 2007-2-26 1:15:43
000000000001 479.12 2007-2-26 2:15:43
000000000001 479.12 2007-2-26 3:15:43
000000000001 479.12 2007-2-26 4:15:43
000000000001 479.12 2007-2-26 5:15:43
000000000001 479.12 2007-2-26 6:15:43
000000000001 479.12 2007-2-26 7:15:43
000000000001 479.12 2007-2-26 8:15:43
000000000001 479.12 2007-2-26 9:15:43
000000000001 479.12 2007-2-26 10:15:43
000000000001 479.12 2007-2-26 11:15:43
000000000001 479.12 2007-2-26 12:15:43
000000000001 479.12 2007-2-26 13:15:43
000000000001 479.12 2007-2-26 14:15:43
000000000001 479.12 2007-2-26 15:15:43
000000000001 479.12 2007-2-26 16:15:43
000000000001 479.12 2007-2-26 17:15:43
000000000001 479.12 2007-2-26 18:15:43
000000000001 479.12 2007-2-26 19:15:43
000000000001 479.12 2007-2-26 20:15:43
000000000001 479.12 2007-2-26 21:15:43
000000000001 479.12 2007-2-26 22:15:43
000000000001 479.12 2007-2-26 23:15:43
现在要将每天的数据分成24点来显示,比如2007-2-26 的数据(见上图),共有24条记录,把这24条记录取出来在一行分24个来显示,对应时间下面是这个时间点的值,如下:
0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '0:00',
max(case when right(convert(varchar(13),readtime,120),2) = '02' then T0 else 0 end) '1:00',
max(case when right(convert(varchar(13),readtime,120),2) = '03' then T0 else 0 end) '2:00',
max(case when right(convert(varchar(13),readtime,120),2) = '04' then T0 else 0 end) '3:00',
max(case when right(convert(varchar(13),readtime,120),2) = '05' then T0 else 0 end) '4:00',
max(case when right(convert(varchar(13),readtime,120),2) = '06' then T0 else 0 end) '5:00',
max(case when right(convert(varchar(13),readtime,120),2) = '07' then T0 else 0 end) '6:00',
max(case when right(convert(varchar(13),readtime,120),2) = '08' then T0 else 0 end) '7:00',
max(case when right(convert(varchar(13),readtime,120),2) = '09' then T0 else 0 end) '8:00',
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '9:00',
max(case when right(convert(varchar(13),readtime,120),2) = '10' then T0 else 0 end) '10:00',
max(case when right(convert(varchar(13),readtime,120),2) = '11' then T0 else 0 end) '11:00',
max(case when right(convert(varchar(13),readtime,120),2) = '12' then T0 else 0 end) '12:00',
max(case when right(convert(varchar(13),readtime,120),2) = '13' then T0 else 0 end) '13:00',
max(case when right(convert(varchar(13),readtime,120),2) = '14' then T0 else 0 end) '14:00',
max(case when right(convert(varchar(13),readtime,120),2) = '15' then T0 else 0 end) '15:00',
max(case when right(convert(varchar(13),readtime,120),2) = '16' then T0 else 0 end) '16:00',
max(case when right(convert(varchar(13),readtime,120),2) = '17' then T0 else 0 end) '17:00',
max(case when right(convert(varchar(13),readtime,120),2) = '18' then T0 else 0 end) '18:00',
max(case when right(convert(varchar(13),readtime,120),2) = '19' then T0 else 0 end) '19:00',
max(case when right(convert(varchar(13),readtime,120),2) = '20' then T0 else 0 end) '20:00',
max(case when right(convert(varchar(13),readtime,120),2) = '21' then T0 else 0 end) '21:00',
max(case when right(convert(varchar(13),readtime,120),2) = '22' then T0 else 0 end) '22:00',
max(case when right(convert(varchar(13),readtime,120),2) = '23' then T0 else 0 end) '23:00'
from tb
group by id
Max(Case When DatePart(hh, ReadTime) = 0 Then T0 Else 0.00 End) As '0:00',
Max(Case When DatePart(hh, ReadTime) = 1 Then T0 Else 0.00 End) As '1:00',
Max(Case When DatePart(hh, ReadTime) = 2 Then T0 Else 0.00 End) As '2:00',
Max(Case When DatePart(hh, ReadTime) = 3 Then T0 Else 0.00 End) As '3:00',
Max(Case When DatePart(hh, ReadTime) = 4 Then T0 Else 0.00 End) As '4:00',
...
Max(Case When DatePart(hh, ReadTime) = 23 Then T0 Else 0.00 End) As '23:00'
From
表
Group By
ID,
Convert(Varchar(10), ReadTime, 120)
create table #tb(ID varchar(20),[T0] numeric(9,2),ReadTime datetime)
insert #tb(ID,[T0],ReadTime)
select '000000000001','479.12','2007-2-26 00:15:43' union all
select '000000000001','479.12','2007-2-26 1:15:43' union all
select '000000000001','479.12','2007-2-26 2:15:43' union all
select '000000000001','479.12','2007-2-26 3:15:43' union all
select '000000000001','479.12','2007-2-26 4:15:43' union all
select '000000000001','479.12','2007-2-26 5:15:43' union all
select '000000000001','479.12','2007-2-26 6:15:43' union all
select '000000000001','479.12','2007-2-26 7:15:43' union all
select '000000000001','479.12','2007-2-26 8:15:43' union all
select '000000000001','479.12','2007-2-26 9:15:43' union all
select '000000000001','479.12','2007-2-26 10:15:43' union all
select '000000000001','479.12','2007-2-26 11:15:43' union all
select '000000000001','479.12','2007-2-26 12:15:43' union all
select '000000000001','479.12','2007-2-26 13:15:43' union all
select '000000000001','479.12','2007-2-26 14:15:43' union all
select '000000000001','479.12','2007-2-26 15:15:43' union all
select '000000000001','479.12','2007-2-26 16:15:43' union all
select '000000000001','479.12','2007-2-26 17:15:43' union all
select '000000000001','479.12','2007-2-26 18:15:43' union all
select '000000000001','479.12','2007-2-26 19:15:43' union all
select '000000000001','479.12','2007-2-26 20:15:43' union all
select '000000000001','479.12','2007-2-26 21:15:43' union all
select '000000000001','479.12','2007-2-26 22:15:43' union all
select '000000000001','479.12','2007-2-26 23:15:43'
go
--执行测试语句
select
max(case when datepart(hh,ReadTime) = 0 then T0 else 0 end) as [0:00]
,max(case when datepart(hh,ReadTime) = 1 then T0 else 0 end) as [1:00]
,max(case when datepart(hh,ReadTime) = 2 then T0 else 0 end) as [2:00]
,max(case when datepart(hh,ReadTime) = 3 then T0 else 0 end) as [3:00]
,max(case when datepart(hh,ReadTime) = 4 then T0 else 0 end) as [4:00]
,max(case when datepart(hh,ReadTime) = 5 then T0 else 0 end) as [5:00]
,max(case when datepart(hh,ReadTime) = 6 then T0 else 0 end) as [6:00]
,max(case when datepart(hh,ReadTime) = 7 then T0 else 0 end) as [7:00]
,max(case when datepart(hh,ReadTime) = 8 then T0 else 0 end) as [8:00]
,max(case when datepart(hh,ReadTime) = 9 then T0 else 0 end) as [9:00]
,max(case when datepart(hh,ReadTime) = 10 then T0 else 0 end) as [10:00]
,max(case when datepart(hh,ReadTime) = 11 then T0 else 0 end) as [11:00]
,max(case when datepart(hh,ReadTime) = 12 then T0 else 0 end) as [12:00]
,max(case when datepart(hh,ReadTime) = 13 then T0 else 0 end) as [13:00]
,max(case when datepart(hh,ReadTime) = 14 then T0 else 0 end) as [14:00]
,max(case when datepart(hh,ReadTime) = 15 then T0 else 0 end) as [15:00]
,max(case when datepart(hh,ReadTime) = 16 then T0 else 0 end) as [16:00]
,max(case when datepart(hh,ReadTime) = 17 then T0 else 0 end) as [17:00]
,max(case when datepart(hh,ReadTime) = 18 then T0 else 0 end) as [18:00]
,max(case when datepart(hh,ReadTime) = 19 then T0 else 0 end) as [19:00]
,max(case when datepart(hh,ReadTime) = 20 then T0 else 0 end) as [20:00]
,max(case when datepart(hh,ReadTime) = 21 then T0 else 0 end) as [21:00]
,max(case when datepart(hh,ReadTime) = 22 then T0 else 0 end) as [22:00]
,max(case when datepart(hh,ReadTime) = 23 then T0 else 0 end) as [23:00]
from #tb t
group by convert(varchar(10),ReadTime,120)
go
--删除测试环境
drop table #tb
go
/*--测试结果
0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12(所影响的行数为 1 行)
*/
insert into tb values('00000000001',479.12,'2007-2-26 00:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 01:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 02:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 03:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 04:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 05:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 06:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 07:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 08:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 09:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 10:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 11:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 12:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 13:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 14:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 15:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 16:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 17:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 18:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 19:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 20:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 21:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 22:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 23:15:43')
go
select id,
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '0:00',
max(case when right(convert(varchar(13),readtime,120),2) = '02' then T0 else 0 end) '1:00',
max(case when right(convert(varchar(13),readtime,120),2) = '03' then T0 else 0 end) '2:00',
max(case when right(convert(varchar(13),readtime,120),2) = '04' then T0 else 0 end) '3:00',
max(case when right(convert(varchar(13),readtime,120),2) = '05' then T0 else 0 end) '4:00',
max(case when right(convert(varchar(13),readtime,120),2) = '06' then T0 else 0 end) '5:00',
max(case when right(convert(varchar(13),readtime,120),2) = '07' then T0 else 0 end) '6:00',
max(case when right(convert(varchar(13),readtime,120),2) = '08' then T0 else 0 end) '7:00',
max(case when right(convert(varchar(13),readtime,120),2) = '09' then T0 else 0 end) '8:00',
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '9:00',
max(case when right(convert(varchar(13),readtime,120),2) = '10' then T0 else 0 end) '10:00',
max(case when right(convert(varchar(13),readtime,120),2) = '11' then T0 else 0 end) '11:00',
max(case when right(convert(varchar(13),readtime,120),2) = '12' then T0 else 0 end) '12:00',
max(case when right(convert(varchar(13),readtime,120),2) = '13' then T0 else 0 end) '13:00',
max(case when right(convert(varchar(13),readtime,120),2) = '14' then T0 else 0 end) '14:00',
max(case when right(convert(varchar(13),readtime,120),2) = '15' then T0 else 0 end) '15:00',
max(case when right(convert(varchar(13),readtime,120),2) = '16' then T0 else 0 end) '16:00',
max(case when right(convert(varchar(13),readtime,120),2) = '17' then T0 else 0 end) '17:00',
max(case when right(convert(varchar(13),readtime,120),2) = '18' then T0 else 0 end) '18:00',
max(case when right(convert(varchar(13),readtime,120),2) = '19' then T0 else 0 end) '19:00',
max(case when right(convert(varchar(13),readtime,120),2) = '20' then T0 else 0 end) '20:00',
max(case when right(convert(varchar(13),readtime,120),2) = '21' then T0 else 0 end) '21:00',
max(case when right(convert(varchar(13),readtime,120),2) = '22' then T0 else 0 end) '22:00',
max(case when right(convert(varchar(13),readtime,120),2) = '23' then T0 else 0 end) '23:00'
from tb
group by iddrop table tb/*
id 0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
00000000001 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12(所影响的行数为 1 行)
*/
insert into tb values('00000000001',479.12,'2007-2-26 00:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 01:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 02:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 03:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 04:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 05:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 06:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 07:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 08:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 09:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 10:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 11:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 12:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 13:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 14:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 15:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 16:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 17:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 18:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 19:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 20:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 21:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 22:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 23:15:43')
go
select id,convert(varchar(10),readtime,120) [day],
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '0:00',
max(case when right(convert(varchar(13),readtime,120),2) = '02' then T0 else 0 end) '1:00',
max(case when right(convert(varchar(13),readtime,120),2) = '03' then T0 else 0 end) '2:00',
max(case when right(convert(varchar(13),readtime,120),2) = '04' then T0 else 0 end) '3:00',
max(case when right(convert(varchar(13),readtime,120),2) = '05' then T0 else 0 end) '4:00',
max(case when right(convert(varchar(13),readtime,120),2) = '06' then T0 else 0 end) '5:00',
max(case when right(convert(varchar(13),readtime,120),2) = '07' then T0 else 0 end) '6:00',
max(case when right(convert(varchar(13),readtime,120),2) = '08' then T0 else 0 end) '7:00',
max(case when right(convert(varchar(13),readtime,120),2) = '09' then T0 else 0 end) '8:00',
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '9:00',
max(case when right(convert(varchar(13),readtime,120),2) = '10' then T0 else 0 end) '10:00',
max(case when right(convert(varchar(13),readtime,120),2) = '11' then T0 else 0 end) '11:00',
max(case when right(convert(varchar(13),readtime,120),2) = '12' then T0 else 0 end) '12:00',
max(case when right(convert(varchar(13),readtime,120),2) = '13' then T0 else 0 end) '13:00',
max(case when right(convert(varchar(13),readtime,120),2) = '14' then T0 else 0 end) '14:00',
max(case when right(convert(varchar(13),readtime,120),2) = '15' then T0 else 0 end) '15:00',
max(case when right(convert(varchar(13),readtime,120),2) = '16' then T0 else 0 end) '16:00',
max(case when right(convert(varchar(13),readtime,120),2) = '17' then T0 else 0 end) '17:00',
max(case when right(convert(varchar(13),readtime,120),2) = '18' then T0 else 0 end) '18:00',
max(case when right(convert(varchar(13),readtime,120),2) = '19' then T0 else 0 end) '19:00',
max(case when right(convert(varchar(13),readtime,120),2) = '20' then T0 else 0 end) '20:00',
max(case when right(convert(varchar(13),readtime,120),2) = '21' then T0 else 0 end) '21:00',
max(case when right(convert(varchar(13),readtime,120),2) = '22' then T0 else 0 end) '22:00',
max(case when right(convert(varchar(13),readtime,120),2) = '23' then T0 else 0 end) '23:00'
from tb
group by id,convert(varchar(10),readtime,120)drop table tb
insert into tb values('00000000001',479.11,'2007-2-25 00:15:43')
insert into tb values('00000000001',479.11,'2007-2-25 01:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 00:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 01:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 02:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 03:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 04:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 05:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 06:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 07:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 08:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 09:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 10:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 11:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 12:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 13:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 14:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 15:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 16:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 17:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 18:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 19:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 20:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 21:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 22:15:43')
insert into tb values('00000000001',479.12,'2007-2-26 23:15:43')
go
select id,convert(varchar(10),readtime,120) [day],
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '0:00',
max(case when right(convert(varchar(13),readtime,120),2) = '02' then T0 else 0 end) '1:00',
max(case when right(convert(varchar(13),readtime,120),2) = '03' then T0 else 0 end) '2:00',
max(case when right(convert(varchar(13),readtime,120),2) = '04' then T0 else 0 end) '3:00',
max(case when right(convert(varchar(13),readtime,120),2) = '05' then T0 else 0 end) '4:00',
max(case when right(convert(varchar(13),readtime,120),2) = '06' then T0 else 0 end) '5:00',
max(case when right(convert(varchar(13),readtime,120),2) = '07' then T0 else 0 end) '6:00',
max(case when right(convert(varchar(13),readtime,120),2) = '08' then T0 else 0 end) '7:00',
max(case when right(convert(varchar(13),readtime,120),2) = '09' then T0 else 0 end) '8:00',
max(case when right(convert(varchar(13),readtime,120),2) = '01' then T0 else 0 end) '9:00',
max(case when right(convert(varchar(13),readtime,120),2) = '10' then T0 else 0 end) '10:00',
max(case when right(convert(varchar(13),readtime,120),2) = '11' then T0 else 0 end) '11:00',
max(case when right(convert(varchar(13),readtime,120),2) = '12' then T0 else 0 end) '12:00',
max(case when right(convert(varchar(13),readtime,120),2) = '13' then T0 else 0 end) '13:00',
max(case when right(convert(varchar(13),readtime,120),2) = '14' then T0 else 0 end) '14:00',
max(case when right(convert(varchar(13),readtime,120),2) = '15' then T0 else 0 end) '15:00',
max(case when right(convert(varchar(13),readtime,120),2) = '16' then T0 else 0 end) '16:00',
max(case when right(convert(varchar(13),readtime,120),2) = '17' then T0 else 0 end) '17:00',
max(case when right(convert(varchar(13),readtime,120),2) = '18' then T0 else 0 end) '18:00',
max(case when right(convert(varchar(13),readtime,120),2) = '19' then T0 else 0 end) '19:00',
max(case when right(convert(varchar(13),readtime,120),2) = '20' then T0 else 0 end) '20:00',
max(case when right(convert(varchar(13),readtime,120),2) = '21' then T0 else 0 end) '21:00',
max(case when right(convert(varchar(13),readtime,120),2) = '22' then T0 else 0 end) '22:00',
max(case when right(convert(varchar(13),readtime,120),2) = '23' then T0 else 0 end) '23:00'
from tb
group by id,convert(varchar(10),readtime,120)drop table tb
/*
id day 0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
-------------------- ---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
00000000001 2007-02-25 479.11 .00 .00 .00 .00 .00 .00 .00 .00 479.11 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00
00000000001 2007-02-26 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12(所影响的行数为 2 行)
*/
declare @s varchar(8000)
select @s='select ID'
select @s=@s+',['+t1.dt+']=max(case datepart(hh,ReadTime) when '
+cast(datepart(hh,cast(dt as datetime)) as varchar)
+' then T0 end)' from t1
select @s=@s+' from t,t1 group by ID'
--print(@s)
exec(@s)
create table t1(dt varchar(10))
insert t1 select '0:00'
union all select '1:00'
union all select '2:00'
union all select '3:00'
union all select '4:00'
union all select '5:00'
union all select '6:00'
union all select '7:00'
union all select '8:00'
union all select '9:00'
union all select '10:00'
union all select '11:00'
union all select '12:00'
union all select '13:00'
union all select '14:00'
union all select '15:00'
union all select '16:00'
union all select '17:00'
union all select '18:00'
union all select '19:00'
union all select '20:00'
union all select '21:00'
union all select '22:00'
union all select '23:00'--测试数据:
create table t(ID varchar(20),T0 numeric(9,2),ReadTime datetime)
insert t(ID,T0,ReadTime)
select '000000000001','479.12','2007-2-26 00:15:43' union all
select '000000000001','479.12','2007-2-26 1:15:43' union all
select '000000000001','479.12','2007-2-26 2:15:43' union all
select '000000000001','479.12','2007-2-26 3:15:43' union all
select '000000000001','479.12','2007-2-26 4:15:43' union all
select '000000000001','479.12','2007-2-26 5:15:43' union all
select '000000000001','479.12','2007-2-26 6:15:43' union all
select '000000000001','479.12','2007-2-26 7:15:43' union all
select '000000000001','479.12','2007-2-26 8:15:43' union all
select '000000000001','479.12','2007-2-26 9:15:43' union all
select '000000000001','479.12','2007-2-26 10:15:43' union all
select '000000000001','479.12','2007-2-26 11:15:43' union all
select '000000000001','479.12','2007-2-26 12:15:43' union all
select '000000000001','479.12','2007-2-26 13:15:43' union all
select '000000000001','479.12','2007-2-26 14:15:43' union all
select '000000000001','479.12','2007-2-26 15:15:43' union all
select '000000000001','479.12','2007-2-26 16:15:43' union all
select '000000000001','479.12','2007-2-26 17:15:43' union all
select '000000000001','479.12','2007-2-26 18:15:43' union all
select '000000000001','479.12','2007-2-26 19:15:43' union all
select '000000000001','479.12','2007-2-26 20:15:43' union all
select '000000000001','479.12','2007-2-26 21:15:43' union all
select '000000000001','479.12','2007-2-26 22:15:43' union all
select '000000000001','479.12','2007-2-26 23:15:43'declare @s varchar(8000)
select @s='select ID'
select @s=@s+',['+t1.dt+']=max(case datepart(hh,ReadTime) when '
+cast(datepart(hh,cast(dt as datetime)) as varchar)
+' then T0 end)' from t1
select @s=@s+' from t,t1 group by ID'
--print(@s)
exec(@s)
drop table t
drop table t1
--测试结果:
/*
ID 0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
000000000001 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12 479.12*/
对于要分的字段可变的情况就用存储过程。
000000000001479.12 2007-2-26 00:15:43
000000000001479.12 2007-2-26 00:15:45
000000000001479.12 2007-2-26 00:15:48
000000000001479.12 2007-2-26 1:15:43
000000000001479.12 2007-2-26 2:15:43
000000000001479.12 2007-2-26 3:15:43
000000000001479.12 2007-2-26 4:15:43
000000000001479.12 2007-2-26 5:15:43注意记录,怎样去除重复的呢?