行列轉換問題
現有用SQL查詢出的數據如下，請問如何寫SQL才能得到轉換後的結果呢
IDValue
C10
C21
C320.00
C110
C211
C312
轉換後
C1C2C3
0120
101112
[解决办法]
--借用臨時表
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 表
--結果
/*
C1C2C3
0120
101112
*/
