create table t1(ID varchar(4),Date varchar(10),Time varchar(6))
insert into t1 select '001','2006-01-01','07:50'
insert into t1 select '001','2006-01-01','08:00'
insert into t1 select '001','2006-01-02','15:30'
insert into t1 select '002','2006-01-01','08:00'
go
create function f_str(@ID varchar(4),@Date varchar(10))
returns varchar(20)
as
begin
declare @ret varchar(20)
set @ret=''
select @ret=@ret+Time+' ' from t1 where ID=@ID and Date=@Date order by Time
return @ret
end
go
declare @sql varchar(8000)
set @sql='select ID'
select @sql=@sql+',['+Date+']=dbo.f_str(ID,'''+Date+''')' from t1 group by Date
set @sql=@sql+' from t1 group by ID'
exec(@sql)
create table tb(ID varchar(20),[Date] varchar(20),[Time] varchar(20))
insert into tb select '001', ' 2006-01-01' ,'07:50'
union all select '001' , '2006-01-01' , '08:00'
union all select '001' , '2006-01-02' , '15:30'
union all select '002' , '2006-01-01' , '08:00'
declare @sql varchar(8000)
set @sql='select ID'
select @sql=@sql+',['+[Date]+']=max(case [Date] when '''+[Date]+''' then [Time] else null end)' from tb group by [Date]
exec(@sql+' from tb group by ID')