SQL SERVER中行列转换

 1 DECLARE @subject varchar(2000)
 2 DECLARE @name varchar(2000)
 3 DECLARE @colName varchar(500)  --声明@colName变量，获取列名科目
 4 set @colName = ''
 5 
 6 --INFORMATION_SCHEMA.COLUMNS数据库的表名称，获取表名为A_Table中列名不为name的其他列名,@colName=[Chinese],[Math],[English],[History],[Geography],[Organism],[Chymistry],
 7 SELECT @colName=@colName+'['+COLUMN_NAME+'],' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='A_Table' AND COLUMN_NAME!='name'
 8 
 9 --从表A_Table中获取字段Name的值,@name=[张三],[李四],[王五],[赵六],
10 select @name=LEFT(name,LEN(name)-1) from (select (SELECT '['+Name+'],' FROM A_Table FOR XML PATH('')) as name) as a
11 
12 --@subject=[Chinese],[Math],[English],[History],[Geography],[Organism],[Chymistry]
13 select @subject=subString(@colName,1,len(@colName)-1)
14 
15 --UNPIVOT用于列转行
16 --PIVOT用于将列值旋转为列名（即行转列）,语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P
17 exec(
18     '
19     select * from
20     (
21         SELECT name,CAST(kemu as FLOAT) as kemu,科目 
22         FROM A_Table as t
23         unpivot(kemu for 科目 in('+@subject+')) as b
24     ) as a
25     pivot(sum(kemu) for name in('+@name+')) as b
26     '
27 )
28 
29 --原表A_Table
30 select * from A_Table

View Code

执行结果如下：

转换后格式
科目	张三	李四	王五	赵六
Chinese	60	70	80	90
Chymistry	60	70	80	90
English	60	70	80	90
Geography	60	70	80	90
History	60	70	80	90
Math	60	70	80	90
Organism	60	70	80	90

原表数据格式
Name	Chinese	Math	English	History	Geography	Organism	Chymistry
张三	60	60	60	60	60	60	60
李四	70	70	70	70	70	70	70
王五	80	80	80	80	80	80	80
赵六	90	90	90	90	90	90	90