sql 行转列

来源：互联网发布：李满春知乎编辑：程序博客网时间：2024/05/05 15:32

CREATE TABLE Scores(StudentID UNIQUEIDENTIFIER PRIMARY KEY,StudentName NVARCHAR(50),Title NVARCHAR(50),Score INT,)INSERT INTO Scores(StudentID,StudentName,Title,Score)VALUES(NEWID(),'张三','语文',90);INSERT INTO Scores(StudentID,StudentName,Title,Score)VALUES(NEWID(),'张三','数学',80);INSERT INTO Scores(StudentID,StudentName,Title,Score)VALUES(NEWID(),'张三','英语',70);INSERT INTO Scores(StudentID,StudentName,Title,Score)VALUES(NEWID(),'李四','语文',60);INSERT INTO Scores(StudentID,StudentName,Title,Score)VALUES(NEWID(),'李四','数学',50);INSERT INTO Scores(StudentID,StudentName,Title,Score)VALUES(NEWID(),'王五','语文',40);SELECT * FROM Scores--DROP TABLE ScoresSELECT StudentName,MAX(CASE Title WHEN '语文' THEN Score ELSE 0 END) AS '语文' ,MAX(CASE Title WHEN '数学' THEN Score ELSE 0 END) AS '数学' ,MAX(CASE Title WHEN '英语' THEN Score ELSE 0 END) AS '英语' FROM ScoresGROUP BY StudentNameORDER BY StudentName

0 0