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

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

/*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

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

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

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’ )

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

go

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图