行列轉換問題
現有用SQL查詢出的數據如下，請問如何寫SQL才能得到轉換後的結果呢

ID Value
C1 0
C2 1
C3 20.00
C1 10
C2 11
C3 12


轉換後

C1 C2 C3
0 1 20
10 11 12


------解决方案--------------------
--借用臨時表
Select ID2 = Identity(Int, 1, 1), * Into #T From 表

--如果ID是固定三個
Select
Max(Case ID When 'C1 ' Then Value Else 0 End) As C1,
Max(Case ID When 'C2 ' Then Value Else 0 End) As C2,
Max(Case ID When 'C3 ' Then Value Else 0 End) As C3
From
(Select OrderID = (Select Count(ID2) From #T Where ID = A.ID And ID2 <= A.ID2), * From #T A) B
Group By
OrderID

--如果ID不是固定三個
Declare @S Varchar(8000)
Select @S = ' '
Select @S = @S + ', Max(Case ID When ' ' ' + ID + ' ' ' Then Value Else 0 End) As [ ' + ID + '] '
From 表 Group By ID
Select @S= 'Select ' + Stuff(@S, 1, 1, ' ') + ' From (Select OrderID = (Select Count(ID2) From #T Where ID = A.ID And ID2 <= A.ID2), * From #T A) B Group By OrderID '
EXEC(@S)

Drop Table #T
------解决方案--------------------
--創建測試環境
Create Table 表
(ID Varchar(10),
Value Int)
--插入數據
Insert 表 Select 'C1 ', 0
Union All Select 'C2 ', 1
Union All Select 'C3 ', 20
Union All Select 'C1 ', 10
Union All Select 'C2 ', 11
Union All Select 'C3 ', 12
GO
--測試
--借用臨時表
Select ID2 = Identity(Int, 1, 1), * Into #T From 表

--如果ID是固定三個
Select
Max(Case ID When 'C1 ' Then Value Else 0 End) As C1,
Max(Case ID When 'C2 ' Then Value Else 0 End) As C2,
Max(Case ID When 'C3 ' Then Value Else 0 End) As C3
From
(Select OrderID = (Select Count(ID2) From #T Where ID = A.ID And ID2 <= A.ID2), * From #T A) B
Group By
OrderID

--如果ID不是固定三個
Declare @S Varchar(8000)
Select @S = ' '
Select @S = @S + ', Max(Case ID When ' ' ' + ID + ' ' ' Then Value Else 0 End) As [ ' + ID + '] '
From 表 Group By ID
Select @S= 'Select ' + Stuff(@S, 1, 1, ' ') + ' From (Select OrderID = (Select Count(ID2) From #T Where ID = A.ID And ID2 <= A.ID2), * From #T A) B Group By OrderID '
EXEC(@S)

Drop Table #T
GO
--刪除測試環境
Drop Table 表
--結果
/*
C1 C2 C3
0 1 20
10 11 12
*/