SQL Table行列转换问题
我现在有一个TABLE   TABLE1   如下
NO         A         B         C    
S1         2         5         3
S2         41       23       72
S3         7         9         65

我要转换成如下表
NO       S1     S2     S3
A         2       41     7
B         5       23     9
C         3       72     65

多谢谢高手支招。

------解决方案--------------------
--建立測試環境
Create Table TABLE1
(NO Varchar(10),
A Int,
B Int,
C Int)
--插入數據
Insert TABLE1 Select 'S1 ', 2, 5, 3
Union All Select 'S2 ', 41, 23, 72
Union All Select 'S3 ', 7, 9, 65
GO
--測試
--如果NO是固定的, 列名已知
Select
Type As NO,
SUM(Case NO When 'S1 ' Then Score Else 0 End) As S1,
SUM(Case NO When 'S2 ' Then Score Else 0 End) As S2,
SUM(Case NO When 'S3 ' Then Score Else 0 End) As S3
From
(
Select NO, 'A ' As Type, A As Score From TABLE1
Union All
Select NO, 'B ' As Type, B As Score From TABLE1
Union All
Select NO, 'C ' As Type, C As Score From TABLE1
) A
Group By
Type

--如果NO不是固定的, 列名已知
Declare @S Varchar(8000)
Select @S = 'Select Type As NO '
Select @S = @S + ', SUM(Case NO When ' ' ' + NO + ' ' ' Then Score Else 0 End) As ' +NO
From TABLE1 Group By NO
Select @S = @S + ' From ( Select NO, ' 'A ' ' As Type, A As Score From TABLE1 Union All Select NO, ' 'B ' ' As Type, B As Score From TABLE1 Union All Select NO, ' 'C ' ' As Type, C As Score From TABLE1 ) A Group By Type '
EXEC(@S)

--如果NO不是固定的, 列名未知
Declare @S1 Varchar(8000), @S2 Varchar(8000)
Select @S1 = 'Select Type As NO ', @S2 = ' '
Select @S1 = @S1 + ', SUM(Case NO When ' ' ' + NO + ' ' ' Then Score Else 0 End) As ' +NO
From TABLE1 Group By NO
Select @S2 = @S2 + ' Union All Select NO, ' ' ' + Name + ' ' ' As Type, ' + Name + ' As Score From TABLE1 '
From SysColumns Where ID = OBJECT_ID( 'TABLE1 ') And Name != 'NO ' Order By ColID
Select @S1 = @S1 + ' From ( ' + Stuff(@S2, 1, 11, ' ') + ') A Group By Type '
EXEC(@S1)
GO
--刪除測試環境
Drop Table TABLE1
--結果
/*
NO S1 S2 S3
A 2 41 7
B 5 23 9
C 3 72 65
*/