表中的数据如下:
datetime name value
2007-6-6 01:00:00 fgh 345
2007-6-6 02:00:00 fgh 532
2007-6-6 03:00:00 fgh 457
......(省略,一共是一天24小时的数据)
2007-6-6 23:00:00 fgh 654生成如下的视图:
date name 一点 二点 三点 ...... 二三点
2007-6-6 fgh 345 532 457 654
datetime name value
2007-6-6 01:00:00 fgh 345
2007-6-6 02:00:00 fgh 532
2007-6-6 03:00:00 fgh 457
......(省略,一共是一天24小时的数据)
2007-6-6 23:00:00 fgh 654生成如下的视图:
date name 一点 二点 三点 ...... 二三点
2007-6-6 fgh 345 532 457 654
as
select cast(convert(varchar(8),[datetime],112) as datetime) as date,
name,
max(case datediff(hour,[datetime]) when 1 then value end) as [一点],
max(case datediff(hour,[datetime]) when 2 then value end) as [二点],
...
max(case datediff(hour,[datetime]) when 23 then value end) as [二三点]
from tablename
group by cast(convert(varchar(8),[datetime],112) as datetime),
nameps:少了0点
as
select cast(convert(varchar(8),[datetime],112) as datetime) as date,
name,
max(case datediff(hour,[datetime]) when 1 then value end) as [一点],
max(case datediff(hour,[datetime]) when 2 then value end) as [二点],
...
max(case datediff(hour,[datetime]) when 23 then value end) as [二三点],
max(case datediff(hour,[datetime]) when 0 then value end) as [二四点]from tablename
group by cast(convert(varchar(8),[datetime],112) as datetime),
name呵呵,小改
(
[datetime] datetime,
name varchar(10),
value int
)
insert into hiyunselect
'2007-6-6 01:00:00', 'fgh' , '345' union all select
'2007-6-6 02:00:00', 'fgh' ,'532' union all select
'2007-6-6 03:00:00' ,'fgh','457'select cast(convert(varchar(8),datetime,112) as datetime) as datetime ,
name,
max(case Datepart(hh,datetime) when 1 then value end) as [一点],
max(case Datepart(hh,datetime) when 2 then value end) as [二点],
......
max(case Datepart(hh,datetime) when 23 then value end) as [二三点],
max(case Datepart(hh,datetime) when 0 then value end) as [二四点] from hiyun
group by cast(convert(varchar(8),datetime,112) as datetime) ,name
name,
max(case Datepart(hh,datetime) when 1 then value end) as [一点],
max(case Datepart(hh,datetime) when 2 then value end) as [二点],
max(case Datepart(hh,datetime) when 3 then value end) as [三点], max(case Datepart(hh,datetime) when 23 then value end) as [二三点],
max(case Datepart(hh,datetime) when 0 then value end) as [二四点] from hiyun
group by convert(varchar(10),datetime,120) ,name