--建立測試環境
Create Table TEST
(a Int,
b Int,
c Int)
--插入數據
Insert TEST Select 123, 22, 33
Union All Select 123, 24, 25
Union All Select 124, 26, 35
Union All Select 124, 26, 25
Union All Select 124, 25, 36
GO
--建立存儲過程
Create ProceDure SP_TEST
As
Begin
Declare @I Int,@MaxCount Int ,@S Varchar(8000)
Select @I=1,@S=''
Select ID=Identity(Int,1,1),* Into #T From TEST
Select *,IDCount=(Select Count(*) From #T Where a=A.a And ID<=A.ID) Into # From #T A
Select @MaxCount=Max(IDCount) From #
While @I<=@MaxCount
Begin
Select @S=@S+',Max(Case IDCount When '+Rtrim(@I)+' Then b Else 0 End) As col'+Rtrim(@I*2-1)+',Max(Case IDCount When '+Rtrim(@I)+' Then c Else 0 End) As col'+Rtrim(@I*2)
Select @I=@I+1
End
Select @S='Select a'+@S+' From # Group By a'
EXEC(@S)
Drop Table #T,#
End
GO
--測試
EXEC SP_TEST
GO
--刪除測試環境
Drop Table TEST
Drop ProceDure SP_TEST
GO
--結果
/*
a col1 col2 col3 col4 col5 col6
123 22 33 24 25 0 0
124 26 35 26 25 25 36
*/