时间 项目1 项目2 项目3 项目4 项目5
2012-1-1 00:00:00 1 2 3 4 5
2012-1-1 01:00:00 6 7 8 9 10
2012-1-1 02:00:00 11 12 13 14 15
2012-1-1 03:00:00 16 17 18 19 20
...取出2011-1-1的格式
时间 0 1 2 3 4 5 6 7 8 9。24
项目1 1 6 11 16
项目2 2 7 12 17
项目3 3 8 13 18
项目4 4 9 14 20
项目5 5 10 20 25
2012-1-1 00:00:00 1 2 3 4 5
2012-1-1 01:00:00 6 7 8 9 10
2012-1-1 02:00:00 11 12 13 14 15
2012-1-1 03:00:00 16 17 18 19 20
...取出2011-1-1的格式
时间 0 1 2 3 4 5 6 7 8 9。24
项目1 1 6 11 16
项目2 2 7 12 17
项目3 3 8 13 18
项目4 4 9 14 20
项目5 5 10 20 25
when 2 then
when 3 then
...
when 24 then
end case
--2012-1-1 00:00:00 1 2 3 4 5
--2012-1-1 01:00:00 6 7 8 9 10
--2012-1-1 02:00:00 11 12 13 14 15
--2012-1-1 03:00:00 16 17 18 19 20
--...
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(时间 varchar(50),项目1 int,项目2 int, 项目3 int, 项目4 int, 项目5 int,)
insert into tb values('2012-1-1 00:00:00', 1, 2 ,3 ,4 ,5)
insert into tb values('2012-1-1 01:00:00', 6, 7 ,8 ,9 ,10)
insert into tb values('2012-1-1 02:00:00', 11, 12, 13, 14 ,15)
insert into tb values('2012-1-1 03:00:00', 16, 17 ,18, 19 ,20)
--取出2011-1-1的格式
--时间 0 1 2 3 4 5 6 7 8 9。24
--项目1 1 6 11 16
--项目2 2 7 12 17
--项目3 3 8 13 18
--项目4 4 9 14 20
--项目5 5 10 20 25
select '项目1'as 时间 ,
max(case DATEPART(HH,时间)when 0 then 项目1 else '0' end ) '0',
max(case DATEPART(HH,时间)when 1 then 项目1 else '0' end ) '1',
max(case DATEPART(HH,时间)when 2 then 项目1 else '0' end ) '2',
max(case DATEPART(HH,时间)when 3 then 项目1 else '0' end ) '3'
from tb t
union all
select '项目2'as 时间 ,
max(case DATEPART(HH,时间)when 0 then 项目2 else '0' end ) '0',
max(case DATEPART(HH,时间)when 1 then 项目2 else '0' end ) '1',
max(case DATEPART(HH,时间)when 2 then 项目2 else '0' end ) '2',
max(case DATEPART(HH,时间)when 3 then 项目2 else '0' end ) '3'
from tb t
union all
select '项目3'as 时间 ,
max(case DATEPART(HH,时间)when 0 then 项目3 else '0' end ) '0',
max(case DATEPART(HH,时间)when 1 then 项目3 else '0' end ) '1',
max(case DATEPART(HH,时间)when 2 then 项目3 else '0' end ) '2',
max(case DATEPART(HH,时间)when 3 then 项目3 else '0' end ) '3'
from tb t
union all
select '项目4'as 时间 ,
max(case DATEPART(HH,时间)when 0 then 项目4 else '0' end ) '0',
max(case DATEPART(HH,时间)when 1 then 项目4 else '0' end ) '1',
max(case DATEPART(HH,时间)when 2 then 项目4 else '0' end ) '2',
max(case DATEPART(HH,时间)when 3 then 项目4 else '0' end ) '3'
from tb t
union all
select '项目5'as 时间 ,
max(case DATEPART(HH,时间)when 0 then 项目5 else '0' end ) '0',
max(case DATEPART(HH,时间)when 1 then 项目5 else '0' end ) '1',
max(case DATEPART(HH,时间)when 2 then 项目5 else '0' end ) '2',
max(case DATEPART(HH,时间)when 3 then 项目5 else '0' end ) '3'
from tb t时间 0 1 2 3
----- ----------- ----------- ----------- -----------
项目1 1 6 11 16
项目2 2 7 12 17
项目3 3 8 13 18
项目4 4 9 14 19
项目5 5 10 15 20(5 行受影响)
max(case datepart(hh,时间) when 0 then 项目1 else null end) [0],
max(case datepart(hh,时间) when 1 then 项目1 else null end) [1],
...
max(case datepart(hh,时间) when 23 then 项目1 else null end) [23]
from tb where datediff(dd,时间,'2012-01-01') = 0
union all
select 时间 = '项目2',
max(case datepart(hh,时间) when 0 then 项目2 else null end) [0],
max(case datepart(hh,时间) when 1 then 项目2 else null end) [1],
...
max(case datepart(hh,时间) when 23 then 项目2 else null end) [23]
from tb where datediff(dd,时间,'2012-01-01') = 0
union all
select 时间 = '项目3',
max(case datepart(hh,时间) when 0 then 项目3 else null end) [0],
max(case datepart(hh,时间) when 1 then 项目3 else null end) [1],
...
max(case datepart(hh,时间) when 23 then 项目3 else null end) [23]
from tb where datediff(dd,时间,'2012-01-01') = 0
union all
select 时间 = '项目4',
max(case datepart(hh,时间) when 0 then 项目4 else null end) [0],
max(case datepart(hh,时间) when 1 then 项目4 else null end) [1],
...
max(case datepart(hh,时间) when 23 then 项目4 else null end) [23]
from tb where datediff(dd,时间,'2012-01-01') = 0union all
select 时间 = '项目5',
max(case datepart(hh,时间) when 0 then 项目5 else null end) [0],
max(case datepart(hh,时间) when 1 then 项目5 else null end) [1],
...
max(case datepart(hh,时间) when 23 then 项目5 else null end) [23]
from tb where datediff(dd,时间,'2012-01-01') = 0
create table tb
(时间 datetime,项目1 int,项目2 int,项目3 int,项目4 int,项目5 int)
insert into tb
select '2012-1-1 00:00:00',1,2,3,4,5 union all
select '2012-1-1 01:00:00',6,7,8,9,10 union all
select '2012-1-1 02:00:00',11,12,13,14,15 union all
select '2012-1-1 03:00:00',16,17,18,19,20 union all
select '2012-1-1 04:00:00',21,22,23,24,25 union all
select '2012-1-1 05:00:00',26,27,28,29,30 union all
select '2012-1-1 06:00:00',31,32,33,34,35 union all
select '2012-1-1 07:00:00',36,37,38,39,40 union all
select '2012-1-1 08:00:00',41,42,43,44,45 union all
select '2012-1-1 09:00:00',46,47,48,49,50 union all
select '2012-1-1 10:00:00',51,52,53,54,55 union all
select '2012-1-1 11:00:00',61,62,63,64,65 union all
select '2012-1-1 12:00:00',71,82,83,84,85 union all
select '2012-1-1 13:00:00',31,42,53,64,75 union all
select '2012-1-1 14:00:00',12,13,14,15,16 union all
select '2012-1-1 15:00:00',23,25,27,28,29 union all
select '2012-1-1 16:00:00',21,22,23,24,25 union all
select '2012-1-1 17:00:00',21,22,23,24,25 union all
select '2012-1-1 18:00:00',21,22,23,24,25 union all
select '2012-1-1 19:00:00',21,22,23,24,25 union all
select '2012-1-1 20:00:00',21,22,23,24,25 union all
select '2012-1-1 21:00:00',21,22,23,24,25 union all
select '2012-1-1 22:00:00',21,22,23,24,25
create table tb2(时间 datetime ,项目 varchar(10),分数 int)
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ', '') + ' select 时间, [项目] = '
+ quotename(name, '''') + ' , [分数] = ' + quotename(name) + ' from tb
where convert(varchar(10),时间,120)=''2012-01-01'''
from syscolumns where name ! = '时间' and id = object_id('tb')
order by colid asc
insert into tb2 exec(@sql)
declare @sql1 varchar(8000)
set @sql1 = 'select [项目]'
select @sql1 = @sql1 + ' , max(case datepart(hh,时间) when ''' + ltrim(sj)
+ ''' then 分数 else 0 end) [' + ltrim(sj) + ']'
from ( select distinct datepart(hh, 时间) as sj from tb2) as a
set @sql1 = @sql1 + 'from tb2 group by [项目]'
exec(@sql1)
drop table tb2/*
项目 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
项目1 1 6 11 16 21 26 31 36 41 46 51 61 71 31 12 23 21 21 21 21 21 21 21
项目2 2 7 12 17 22 27 32 37 42 47 52 62 82 42 13 25 22 22 22 22 22 22 22
项目3 3 8 13 18 23 28 33 38 43 48 53 63 83 53 14 27 23 23 23 23 23 23 23
项目4 4 9 14 19 24 29 34 39 44 49 54 64 84 64 15 28 24 24 24 24 24 24 24
项目5 5 10 15 20 25 30 35 40 45 50 55 65 85 75 16 29 25 25 25 25 25 25 25
*/
replace(convert(varchar(13),cast(datetime1 as datetime),120),convert(varchar(10),cast(datetime1 as datetime),120),convert(varchar(11),cast(datetime1 as datetime),120)) as mytime1
from table1
获取 年 月 日 时 中的 时