数据筛选(对表列的操作,并生成新表),该怎么解决

数据筛选(对表列的操作,并生成新表)
有表A如下：
ID TIME
1 2007-12-27 08:09  
1 2007-12-27 08:09 
1 2007-12-27 08:09  
2 2007-12-27 08:10 
2 2007-12-27 08:10 
3 2007-12-27 08:11  
3 2007-12-27 08:11 
1 2007-12-27 20:17 
1 2007-12-27 20:17 
2 2007-12-27 18:10 
1 2007-12-28 08:09  
2 2007-12-28 08:10 
2 2007-12-28 08:10 
2 2007-12-28 08:10 
3 2007-12-28 08:19  
1 2007-12-28 20:17 
1 2007-12-28 20:17 
2 2007-12-28 18:10 

要求筛选出如下表B：

ID date time
1 2007-12-27 08:09,08:09,08:09,20:17,20:17,
2 2007-12-27 08:10,08:10,18:10,
3 2007-12-27 08:11,08:11,
1 2007-12-28 08:09,20:17,20:17,
2 2007-12-28 08:10,08:10,8:10,18:10,

非常感谢各位高手的指点


[解决办法]
用函数

SQL code

create function fn_Times(@Id int,@Date datetime)returns varchar(200)asbegin     declare @r varchar(200)    select @r=isnull(@r+',','')+convert(varchar(5),[Time],108) from a where id=@id and convert(varchar(10),[Time],120)=@Date    return @rendgo--调用select Id,convert(varchar(10),[Time],120) as [Date],dbo.fn_Times(Id,convert(varchar(10),[Time],120)) as [Time]from agroup by Id,convert(varchar(10),[Time],120)
[解决办法]
SQL code
create table test(Id int,time datetime)insert into test  select  1,         '2007-12-27     08:09'     insert into test  select  1,         '2007-12-27     08:09'   insert into test  select  1,         '2007-12-27     08:09'     insert into test  select  2,         '2007-12-27     08:10'   insert into test  select  2,         '2007-12-27     08:10'   insert into test  select  3,         '2007-12-27     08:11'     insert into test  select  3,         '2007-12-27     08:11'   insert into test  select  1,         '2007-12-27     20:17'   insert into test  select  1,         '2007-12-27     20:17'   insert into test  select  2,         '2007-12-27     18:10'   insert into test  select  1,         '2007-12-28     08:09'     insert into test  select  2,         '2007-12-28     08:10'   insert into test  select  2,         '2007-12-28     08:10'   insert into test  select  2,         '2007-12-28     08:10'   insert into test  select  3,         '2007-12-28     08:19'     insert into test  select  1,         '2007-12-28     20:17'   insert into test  select  1,         '2007-12-28     20:17'   insert into test  select  2,         '2007-12-28     18:10'   Create function F_String(@id int,@date varchar(10))returns varchar(1000)asbegindeclare @v varchar(1000)set @v=''select @v=@v+convert(varchar(5),time,108)+',' from testwhere id=@Id and Convert(varchar(10),time,120)=@datereturn @vendselect distinct Id,Convert(varchar(10),time,120) as date, dbo.F_String(Id,Convert(varchar(10),time,120)) as time from testId          date       time----------- ---------- ----------------------------------------------------------------------------------------------------------------1           2007-12-27 08:09,08:09,08:09,20:17,20:17,1           2007-12-28 08:09,20:17,20:17,2           2007-12-27 08:10,08:10,18:10,2           2007-12-28 08:10,08:10,08:10,18:10,3           2007-12-27 08:11,08:11,3           2007-12-28 08:19,
[解决办法]
SQL code
select id,convert(varchar(10),time,120) as date,       substring(convert(varchar(20),min(time),120),12,5) as starttime,       case when id = 3 and (select count(1) from a                              where id = 3 and convert(varchar(10),time,120) = convert(varchar(10),t1.time,120)) = 1       then null else substring(convert(varchar(20),max(time),120),12,5) end as endtimeinto cfrom a t1group by id,convert(varchar(10),time,120)/*id          date       starttime  endtime----------- ---------- ---------- ----------1           2007-12-27 08:09      20:172           2007-12-27 08:10      18:103           2007-12-27 08:11      08:111           2007-12-28 08:09      20:172           2007-12-28 08:10      18:103           2007-12-28 08:19      NULL(6 row(s) affected)*/