我用的是一种临时表的做法，感觉是特别苯的，不过我自己测试结果没问题~
哪位有什么好方法，希望可以学习学习~~
--1.创建临时表结构
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tmp_TTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tmp_TTable]
GO
create table Tmp_TTable
(
TID varchar(4),
TName varchar(10),
TDate varchar(10)
)
GO
--2.修改临时表结构
declare @MaxNUM int,
@NUM int,
@SQL varchar(255)
select @NUM=1
select @MaxNUM=max(NUM) from (select count(*) as NUM from TTable group by TID,TDate) as A
while @NUM<=@MaxNUM
begin
select @SQL='alter table Tmp_TTable add TTime'+ cast(@NUM as varchar)+ ' varchar(10)'
exec(@SQL)
select @NUM=@NUM+ 1
end
--3.插入数据值
declare @TID varchar(4),
@TName varchar(10),
@TDate varchar(10),
@TTime varchar(10)
insert into Tmp_TTable(TID,TName,TDate) select distinct TID,TName,TDate from TTable
declare MyCurOne scroll cursor for
select TID,TName,TDate from TTable order by TID,TDate
open MyCurOne
fetch next from MyCurOne into @TID,@TName,@TDate
while @@FETCH_STATUS=0
begin
declare MyCurTwo cursor for
select TTime from TTable where TID=@TID and TName=@TName and TDate=@TDate
open MyCurTwo
select @NUM=1
fetch next from MyCurTwo into @TTime
while @@FETCH_STATUS=0
begin
select @SQL='update Tmp_TTable set TTime'+ cast(@NUM as varchar)+ '='''+ @TTime+ ''' where TID='''+ @TID+ ''' and TName='''+ @TName+ ''' and TDate='''+ @TDate+ ''''
exec(@SQL)
fetch next from MyCurTwo into @TTime
select @NUM=@NUM+1
end
close MyCurTwo
deallocate MyCurTwo
fetch relative @NUM from MyCurOne into @TID,@TName,@TDate
end
close MyCurOne
deallocate MyCurOne
GO
--4.显示结果
select * from Tmp_TTable
