请教个竖表转换成横表的问题?
请教各位高手,怎么把A表转换成B表
A表结构如下
name   date             time
a 2007-1-1 7:20
a 2007-1-1 11:35
a 2007-1-1 13:00
a 2007-1-1 17:30
a 2007-1-1 20:15
a 2007-1-1 22:00
a 2007-1-2 7:30
a 2007-1-2 11:30
b 2007-1-1 7:20
b 2007-1-1 11:35
b 2007-1-1 13:00
b 2007-1-1 17: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] 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
*/

------解决方案--------------------
/*鱼老大学习了
我以前都是在表上借助辅助列的
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 (