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
执行结果如下：
| 转换后格式 | ||||
| 科目 | 张三 | 李四 | 王五 | 赵六 |
| 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 |
