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 a11 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 P17 exec(18 '19 select * from20 (21 SELECT name,CAST(kemu as FLOAT) as kemu,科目 22 FROM A_Table as t23 unpivot(kemu for 科目 in('+@subject+')) as b24 ) as a25 pivot(sum(kemu) for name in('+@name+')) as b26 '27 )28 29 --原表A_Table30 select * from A_Table
执行结果如下：
| 转换后格式 | ||||
| 科目 | 张三 | 李四 | 王五 | 赵六 |
| 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 |