在sql server 2005下如何搭建这样的视图？
表中的数据如下：
datetimenamevalue
2007-6-6 01:00:00fgh345
2007-6-6 02:00:00fgh532
2007-6-6 03:00:00fgh457
......(省略，一共是一天24小时的数据)
2007-6-6 23:00:00fgh654
生成如下的视图：
date name一点二点三点......二三点
2007-6-6 fgh345532457654
[解决办法]
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
