sql 问题,,????
我有一表,,t1格式为:
no                     time
001       2007-03-04   07:50
001       2007-03-04   13:25
001       2007-03-04   17:50
002       2007-03-04   07:50
002       2007-03-04   13:22
003       2007-03-04   17:56
我想把演变成另一个表,t2格式:
no           date             time1     time2       time3
001     2007-03-04     07:50     13:25       17:50
002     2007-03-04     07:50     13:22       17:56
t2的数据是从t1中来!!!如何做???

------解决方案--------------------
/*
返回时间区域代码
*/
CREATE FUNCTION [dbo].[GetCheckOnCode]
(
@time VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @str VARCHAR(50)
DECLARE @D SMALLDATETIME
SET @D=CONVERT(SMALLDATETIME,@time,8)
IF DATEDIFF(SS, '00:00:00 ',@D)> =0 AND DATEDIFF(SS, '12:00:00 ',@D) <=0
SET @str= 'time1 '
ELSE IF DATEDIFF(SS, '12:00:00 ',@D)> =0 AND DATEDIFF(SS, '17:00:00 ',@D) <=0
SET @str= 'time2 '
ELSE IF DATEDIFF(SS, '17:00:00 ',@D)> =0 AND DATEDIFF(SS, '23:00:00 ',@D) <=0
SET @str= 'time3 '
ELSE
SET @str= 'time4 '
RETURN @str
END
go

create table t1(no1 varchar(10),datetime1 datetime)
go
insert t1 values( '001 ', '2007-03-04 07:50 ')
insert t1 values( '001 ', '2007-03-04 13:25 ')
insert t1 values( '001 ', '2007-03-04 17:50 ')
insert t1 values( '002 ', '2007-03-04 07:50 ')
insert t1 values( '002 ', '2007-03-04 13:22 ')
insert t1 values( '002 ', '2007-03-04 17:56 ')
go

select * from t1
go


select no1,[dd],
[time1]=max(case when tag= 'time1 ' then tt end),
[time2]=max(case when tag= 'time2 ' then tt end),
[time3]=max(case when tag= 'time3 ' then tt end)
from (
select no1,[dd]=CONVERT(VARCHAR(10),datetime1,120),[tt]=CONVERT(VARCHAR(10),datetime1,8),
[tag]=[dbo].[GetCheckOnCode](CONVERT(VARCHAR(10),datetime1,8))
from t1
) t
group by no1,[dd]
go

drop table t1
go