在sql server 2005下如何搭建这样的视图？
表中的数据如下：
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

------解决方案--------------------
create view v_test
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),
name


ps:少了0点



------解决方案--------------------
create view v_test
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

呵呵，小改
------解决方案--------------------
create table hiyun
(
[datetime] datetime,
name varchar(10),
value int
)


insert into hiyun

select
'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