Create Table Tab
(Co1 Varchar(10),
Co2 Varchar(10),
Co3 Varchar(10))
Insert Tab Select 'a1', 'kkk', 'p500'
Union All Select 'a1', 'fff', 'p300'
Union All Select 'a1', 'eee', 'p600'
Union All Select 'a2', 'ccc', 'p200'
Union All Select 'a2', 'fff', 'p100'
Union All Select 'a2', 'eee', 'p300'
GO
Select ID=Identity(Int,1,1),* Into #T From Tab
Select *,IDCount=(Select Count(*) From #T Where Co1=A.Co1 And ID<=A.ID) Into # From #T A
Declare @S Varchar(8000)
Select @S=''
Select @S=@S+',Max(Case Co2 When '''+Co2+''' Then Co3 Else '''' End) As '+Co2
From # Group By Co2 Order By Min(IDCount),Min(Co1)
Select @S='Select Co1'+@S+' From Tab Group By Co1'
EXEC(@S)
Drop Table #T,#
GO
Drop Table Tab
--Result
/*
Co1 kkk ccc fff eee
a1 p500 p300 p600
a2 p200 p100 p300
*/
--如果沒有明顯的可以用來排序的列,就借用臨時表了
Create Table Tab
(Co1 Varchar(10),
Co2 Varchar(10),
Co3 Varchar(10))
Insert Tab Select 'a1', 'ccc', 'p500'
Union All Select 'a1', 'fff', 'p300'
Union All Select 'a1', 'eee', 'p600'
Union All Select 'a2', 'ccc', 'p200'
Union All Select 'a2', 'fff', 'p100'
Union All Select 'a2', 'eee', 'p300'
GO
Select ID=Identity(Int,1,1),* Into #T From Tab
Declare @S Varchar(8000)
Select @S=''
Select @S=@S+',Max(Case Co2 When '''+Co2+''' Then Co3 Else '''' End) As '+Co2
From #T Group By Co2 Order By Min(ID)
Select @S='Select Co1'+@S+' From Tab Group By Co1'
EXEC(@S)
Drop Table #T
GO
Drop Table Tab
--Result
/*
Co1 ccc fff eee
a1 p500 p300 p600
a2 p200 p100 p300
*/