浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用

这篇文章主要为大家详细介绍了浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

/*创建数据库*/CREATE DATABASE tmpgoUSE tmpgo

/*创建数据库测试表*/CREATE TABLE [Scores] ( [ID] INT IDENTITY(1, 1) PRIMARY KEY , [Student] VARCHAR(20) , [Subject] VARCHAR(30) , [Score] FLOAT )

go

TRUNCATE 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 ScoresGROUP BY StudentORDER BY Student

/*2. pivot(聚合函数(要转成列值的列名)　 for 要转换的列　　 in（目标列名）　　)*/

SELECT Student AS '姓名' , AVG(语文) AS '语文' , AVG(英语) AS '英语'FROM Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScoresGROUP BY StudentORDER BY Student ASC

注：关于浅析SQL语句行列转换的两种方法 case...when与pivot函数的应用的内容就先介绍到这里，更多相关文章的可以留意