行列
有用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
*/