浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用
SQL语句行列转换的两种方法 case...when和pivot函数应用，运用pivot 函数只支持数据库版本2005以上的。一般运用case when else end 的方法比较多，比较普遍

/*创建数据库*/ CREATE DATABASE tmp go USE tmp go/*创建数据库测试表*/CREATE TABLE [Scores]     (       [ID] INT IDENTITY(1, 1)                PRIMARY KEY ,       [Student] VARCHAR(20) ,       [Subject] VARCHAR(30) ,       [Score] FLOAT     )goTRUNCATE TABLE Scores /*插入数据库测试数据信息*/  INSERT  INTO Scores         ( Student, Subject, Score ) VALUES  ( 'test001', '语文', '90' ) INSERT  INTO Scores         ( Student, Subject, Score ) VALUES  ( 'test001', '英语', '85' ) INSERT  INTO Scores         ( Student, Subject, Score ) VALUES  ( 'text002', '语文', '90' ) INSERT  INTO Scores         ( Student, Subject, Score ) VALUES  ( 'text002', '英语', '80' ) INSERT  INTO Scores         ( Student, Subject, Score ) VALUES  ( 'test003', '语文', '95' ) INSERT  INTO Scores         ( Student, Subject, Score ) VALUES  ( 'test003', '英语', '85' )/*1.  case when .......then else  ....end 用法，行列转换*/ SELECT  Student AS '姓名' ,         MAX(CASE Subject               WHEN '语文' THEN Score               ELSE 0             END) AS '语文' ,--如果这个行是“语文”，就选此行作为列        MAX(CASE Subject               WHEN '英语' THEN Score               ELSE 0             END) AS '英语'FROM    Scores GROUP BY Student ORDER BY Student/*2. pivot(聚合函数(要转成列值的列名)　      for 要转换的列 　　    in（目标列名） 　　)*/--by www.jbxue.comSELECT  Student AS '姓名' ,         AVG(语文) AS '语文' ,         AVG(英语) AS '英语'FROM    Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScores GROUP BY Student ORDER BY Student ASC