sql查询

id(auto)  name                      date               time1            time2        
1     BICHLAM,NGUYEN        2010-12-16     08:47:00           12:04:00
2     BICHLAM,NGUYEN        2010-12-16     12:55:00           17:40:00
3     BICHLAM,NGUYEN        2010-12-17     09:39:00       13:13:00
4     BICHLAM,NGUYEN        2010-12-17     14:18:00           18:43:00


求sql查询结果
BICHLAM,NGUYEN         2010-12-16         08:47:00  12:04:00 12:55:00  17:40:00
BICHLAM,NGUYEN         2010-12-17         09:39:00  13:13:00 14:18:00  18:43:00

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table [TB]([id] int,[name] varchar(24),[date] varchar(10),[time1] varchar(8),[time2] varchar(8))
insert [TB]
select 1,'BICHLAM,NGUYEN','2010-12-16','08:47:00','12:04:00' union all
select 2,'BICHLAM,NGUYEN','2010-12-16','12:55:00','17:40:00' union all
select 3,'BICHLAM,NGUYEN','2010-12-17','09:39:00','13:13:00' union all
select 4,'BICHLAM,NGUYEN','2010-12-17','14:18:00','18:43:00'
GO

create function dbo.f_str(@name varchar(20),@date varchar(10)) returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str + ' ' , '') + cast(time1 as varchar) + ' ' + cast(time2 as varchar) from tb where name = @name and date = @date
  return @str
end
go

--调用函数
select name , date,time = dbo.f_str(name,date) from tb group by name,date

drop function dbo.f_str

drop table tb

/*
name                     date       time                              
------------------------ ---------- -----------------------------------
BICHLAM,NGUYEN           2010-12-16 08:47:00 12:04:00 12:55:00 17:40:00
BICHLAM,NGUYEN           2010-12-17 09:39:00 13:13:00 14:18:00 18:43:00

（所影响的行数为 2 行）
*/

2005

 

create table [TB]([id] int,[name] varchar(24),[date] varchar(10),[time1] varchar(8),[time2] varchar(8))
insert [TB]
select 1,'BICHLAM,NGUYEN','2010-12-16','08:47:00','12:04:00' union all
select 2,'BICHLAM,NGUYEN','2010-12-16','12:55:00','17:40:00' union all
select 3,'BICHLAM,NGUYEN','2010-12-17','09:39:00','13:13:00' union all
select 4,'BICHLAM,NGUYEN','2010-12-17','14:18:00','18:43:00'
GO

select name,date, [time] = stuff((select ' ' + [time1] + ' ' + [time2] from tb t where name = tb.name and date = tb.date for xml path('')) , 1 , 1 , '')
from tb
group by name,date

drop table tb

/*
name                     date       time
------------------------ ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BICHLAM,NGUYEN           2010-12-16 08:47:00 12:04:00 12:55:00 17:40:00
BICHLAM,NGUYEN           2010-12-17 09:39:00 13:13:00 14:18:00 18:43:00

(2 行受影响)
*/