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')drop table tb
--这样?
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')drop table tb
--这样?
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'
gocreate 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)/*
ID 2006-01-01 2006-01-02
---- -------------------- --------------------
001 07:50 08:00 15:30
002 08:00
*/drop function f_str
drop table t1
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'
goSELECT * FROM T1
PIVOT
(
DBO.SQL_AGGREGATE(Time)
FOR Date IN ([2006-01-01],[2006-01-02])
) AS PIT--结果
/*
ID 2006-01-01 2006-01-02
----- ----------- --------------
001 07:50 08:00 15:30
002 08:00
*/