sql server 行转列及列转行

       图1 

       图2

1.行转列 （图1->图2）

1.方法一 (数据库通用），使用max 加case when 函数

-- 行转列  图1->图2
SELECT name,MAX(CASE WHEN subject='语文' THEN score ELSE 0 END) AS "语文",MAX(CASE WHEN subject='数学' THEN score ELSE 0 END) AS "数学",MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) AS "英语"
FROM student
GROUP BY name;

运行结果： 

2.方法二，使用PIVOT

SELECT   name,  [语文],  [数学],  [英语]  
FROM   
(  SELECT name, subject, score  FROM student  
) AS SourceTable  
PIVOT  
(  MAX(score) -- 使用 MAX 因为 PIVOT 需要一个聚合函数  FOR subject IN ([语文], [数学], [英语])  
) AS PivotTable;

运行结果：  

 3.测试表及数据

CREATE TABLE student (  NAME VARCHAR(50),  SUBJECT VARCHAR(50),  SCORE INT 
);INSERT INTO student (NAME, SUBJECT, SCORE) VALUES  
('小明', '语文', 96),  
('小明', '数学', 98),    
('小明', '英语', 95), 
('大花', '语文', 92), 
('大花', '数学', 96),  
('大花', '英语', 98);   

 2.列转行（图2->图1）

1.方法一 （数据库通用）使用union 来拼接

SELECT NAME, '语文' AS subject , MAX("语文") AS score
FROM student_scores GROUP BY NAME
UNION 
SELECT NAME, '数学' AS subject , MAX("数学") AS score
FROM student_scores GROUP BY NAME
UNION
SELECT  NAME, '英语' AS subject , MAX("英语") AS score
FROM student_scores GROUP BY NAME;

运行结果： 

 

2.方法二，使用UNPIVOT

​​-- 在SQL Server中使用的UNPIVOT示例  
SELECT   name,  subject,  score  
FROM   
(  SELECT name, [语文], [数学], [英语]  FROM student_scores  
) AS SourceTable  
UNPIVOT  
(  score FOR subject IN ([语文], [数学], [英语])  
) AS UnpivotTable;

运行结果： 

3.测试表及数据

CREATE TABLE student_scores (  name VARCHAR(50),  语文 INT, 数学 INT,   英语 INT 
);INSERT INTO student_scores (name, 语文, 数学, 英语) VALUES  
('大花', 92, 96, 98),  
('小明', 96, 98, 95);