行转列PIVOT用法

新建StudentScore表：

CREATE TABLE [dbo].[StudentScore](
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [StudentCard] [varchar](50) NOT NULL,
    [StudentName] [nvarchar](5) NOT NULL,
    [Type]  [nvarchar](10) NOT NULL,
    [Course] [nvarchar](10) NOT NULL,
    [Score] [float] NOT NULL,     
) 

 

插入测试数据：

INSERT INTO StudentScore VALUES('001','小明','考试1','语文',80)
INSERT INTO StudentScore VALUES('002','小红','考试1','语文',60)
INSERT INTO StudentScore VALUES('003','小王','考试1','语文',55)
INSERT INTO StudentScore VALUES('004','小龙','考试1','语文',90)
INSERT INTO StudentScore VALUES('005','小军','考试1','语文',72)

INSERT INTO StudentScore VALUES('001','小明','考试1','数学',90)
INSERT INTO StudentScore VALUES('002','小红','考试1','数学',65)
INSERT INTO StudentScore VALUES('003','小王','考试1','数学',52)
INSERT INTO StudentScore VALUES('004','小龙','考试1','数学',91)
INSERT INTO StudentScore VALUES('005','小军','考试1','数学',82)

INSERT INTO StudentScore VALUES('001','小明','考试1','英语',70)
INSERT INTO StudentScore VALUES('002','小红','考试1','英语',80)
INSERT INTO StudentScore VALUES('003','小王','考试1','英语',75)
INSERT INTO StudentScore VALUES('004','小龙','考试1','英语',60)
INSERT INTO StudentScore VALUES('005','小军','考试1','英语',62)


INSERT INTO StudentScore VALUES('001','小明','考试2','语文',82)
INSERT INTO StudentScore VALUES('002','小红','考试2','语文',68)
INSERT INTO StudentScore VALUES('003','小王','考试2','语文',58)
INSERT INTO StudentScore VALUES('004','小龙','考试2','语文',93)
INSERT INTO StudentScore VALUES('005','小军','考试2','语文',75)

INSERT INTO StudentScore VALUES('001','小明','考试2','数学',92)
INSERT INTO StudentScore VALUES('002','小红','考试2','数学',69)
INSERT INTO StudentScore VALUES('003','小王','考试2','数学',58)
INSERT INTO StudentScore VALUES('004','小龙','考试2','数学',93)
INSERT INTO StudentScore VALUES('005','小军','考试2','数学',88)

INSERT INTO StudentScore VALUES('001','小明','考试2','英语',77)
INSERT INTO StudentScore VALUES('002','小红','考试2','英语',88)
INSERT INTO StudentScore VALUES('003','小王','考试2','英语',78)
INSERT INTO StudentScore VALUES('004','小龙','考试2','英语',66)
INSERT INTO StudentScore VALUES('005','小军','考试2','英语',63)

 

查看各科目最大的成绩：

SELECT [语文],[数学],[英语] FROM
(
    select Course,Score from StudentScore  
) AS s
PIVOT
(
    MAX(Score) FOR Course IN ([语文],[数学],[英语])
) AS t

结果如下：

 

查看每次考试各科最高成绩：

SELECT [Type],[语文],[数学],[英语] FROM
(
    select [Type],Course,Score from StudentScore  
) AS s
PIVOT
(
    MAX(Score) FOR Course IN ([语文],[数学],[英语])
) AS t

结果如下：

 

需要注意的是，当表里不是只有查询所用到的列Type、Score、Course时， 这里需要加子查询并指定查询的列名，否则会出现这种情况

SELECT [Type],[语文],[数学],[英语] FROM StudentScore
PIVOT
(
    MAX(Score) FOR Course IN ([语文],[数学],[英语])
) AS pvt