请教个竖表转换成横表的问题?
请教各位高手,怎么把A表转换成B表
A表结构如下
name date time
a2007-1-17:20
a2007-1-111:35
a2007-1-113:00
a2007-1-117:30
a2007-1-120:15
a2007-1-122:00
a2007-1-27:30
a2007-1-211:30
b2007-1-17:20
b2007-1-111:35
b2007-1-113:00
b2007-1-117:30


想要转换成B表:
name　　date time1　　time2　　time3 time4 time5 time6
A　　　2007-1-1　　　7:20　　11:35 13:00 17:30 20:15 22:00
A　　　2007-1-2 7:30 11:30
B　　　2007-1-1　　　7:20　　11:35 13:00 17:30

[解决办法]
--建境
Create Table A
(name Varchar(10),
[date] Varchar(10),
[time] Varchar(10))
--插入
Insert A Select 'a ', '2007-1-1 ', '7:20 '
Union All Select 'a ', '2007-1-1 ', '11:35 '
Union All Select 'a ', '2007-1-1 ', '13:00 '
Union All Select 'a ', '2007-1-1 ', '17:30 '
Union All Select 'a ', '2007-1-1 ', '20:15 '
Union All Select 'a ', '2007-1-1 ', '22:00 '
Union All Select 'a ', '2007-1-2 ', '7:30 '
Union All Select 'a ', '2007-1-2 ', '11:30 '
Union All Select 'b ', '2007-1-1 ', '7:20 '
Union All Select 'b ', '2007-1-1 ', '11:35 '
Union All Select 'b ', '2007-1-1 ', '13:00 '
Union All Select 'b ', '2007-1-1 ', '17:30 '
GO
--
Select ID = Identity(Int, 1, 1), * Into #T From A

Declare @S Varchar(8000)
Select @S = 'Select name, [date] '
Select @S = @S + ', Max(Case OrderID When ' + Cast(OrderID As Varchar) + ' Then time Else ' ' ' ' End) As time ' + Cast(OrderID As Varchar)
From (Select OrderID = (Select Count(name) From #T Where name = B.name And [date] = B.[date] And ID <= B.ID), * From #T B) T Group By OrderID
Select @S = @S + ' From (Select OrderID = (Select Count(name) From #T Where name = B.name And [date] = B.[date] And ID <= B.ID), * From #T B) T Group By name, [date] Order By name, [date] '
EXEC(@S)

Drop Table #T
GO
--除境
Drop Table A
--果
/*
name[date]time1time2time3time4time5time6
a2007-1-17:2011:3513:0017:3020:1522:00
a2007-1-27:3011:30
b2007-1-17:2011:3513:0017:30
*/

[解决办法]
/*鱼老大学习了
我以前都是在表上借助辅助列的
1、
Alter Table A Add ID int
2、Declare @id int
Set @id=0
Update A Set ID=@id,@id=@id+1
3、Update B
Set ID=(Select Count(1) From A
Where Name=B.Name And Date=B.Date And ID <=B.ID) From A B
然后再----哎！！！！
作个记号*/
----------------------------
Select ID=Identity(int,1,1),* Into #A From A

Select * From #A
Declare @sql Varchar(8000)
Set @sql= ' '
Select @sql=@sql+ ',Max(Case OrderID When ' ' '+Cast(OrderID As Varchar)+ ' ' ' Then Time Else ' ' ' ' End) As[Time '+Cast(OrderID As Varchar)+ '] '
From (

Select OrderID=(Select
Count(Name)
From
#A
Where Name=B.Name And Date=B.Date And ID <=B.ID),
* From #A B) T Group By OrderID
Exec( 'Select Name,Convert(Varchar(10),date,120) As [Date] '+@sql+ 'From (
Select OrderID=(Select
Count(Name)
From
#A
Where Name=B.Name And Date=B.Date And ID <=B.ID),
* From #A B) T Group By Name,Convert(Varchar(10),date,120) Order By Name ')