Stay hungry,Stay foolish!

0%

SQL Server实现转置

工作中经常需要查询一些数据,需要写sql语句来写报表。最近就在业务中使用到了转置,现在已经不再使用case方式了,而是使用sql server内置的pivot函数来实现转置。
业务很逻辑,需要从多个表中把数据查出来,分组、排序等等,但是主要内容还是不变的,这里使用一些测试数据来测试。

1 业务场景

现在的场景是:一个学生有多门课程,每门课程都有成绩,数据库中每一行数据如下:

1
学号    姓名    科目    成绩
2
01      Huang   Math    90

多行数据的时候:

1
学号    姓名    科目    成绩
2
01      Huang   Math    90
3
01      Huang   English 90
4
02      Yang    Math    95
5
02      Yang    English 93

现在的要求是要预览每个同学的各科成绩,也就是把一个学生的所有成绩压缩到一行显示,如下:

1
学号    姓名    Math  English
2
01      Huang   90    90

2 测试脚本

sql脚本如下,先去测试数据库执行一下语句。

1
GO
2
/****** Object:  Table [dbo].[Scores]    Script Date: 12/30/2019 2:44:21 PM ******/
3
SET ANSI_NULLS ON
4
GO
5
SET QUOTED_IDENTIFIER ON
6
GO
7
CREATE TABLE [dbo].[Scores](
8
    [stuNo] [varchar](50) NULL,
9
    [stuName] [varchar](50) NULL,
10
    [subject] [varchar](50) NULL,
11
    [score] [varchar](50) NULL
12
) ON [PRIMARY]
13
GO
14
INSERT [dbo].[Scores] ([stuNo], [stuName], [subject], [score]) VALUES (N'1001', N'Zhang Wei', N'Math', N'78')
15
INSERT [dbo].[Scores] ([stuNo], [stuName], [subject], [score]) VALUES (N'1001', N'Zhang Wei', N'English', N'81')
16
INSERT [dbo].[Scores] ([stuNo], [stuName], [subject], [score]) VALUES (N'1001', N'Zhang Wei', N'Science', N'90')
17
INSERT [dbo].[Scores] ([stuNo], [stuName], [subject], [score]) VALUES (N'1002', N'Wang Hao', N'English', N'70')
18
INSERT [dbo].[Scores] ([stuNo], [stuName], [subject], [score]) VALUES (N'1002', N'Wang Hao', N'Science', N'87')
19
INSERT [dbo].[Scores] ([stuNo], [stuName], [subject], [score]) VALUES (N'1002', N'Wang Hao', N'Math', N'76')

插入的数据如下:
微信图片_201912301639382019-12-30-16-51-52

3 PIVOT函数转置

执行一下查询:

1
SELECT * FROM dbo.Scores WITH (NOLOCK)
2
PIVOT(MAX(score) FOR subject IN ("Math","English","Science")) AS T

结果如下:
微信图片_201912301639472019-12-30-16-52-4

4 总结

暂时先写到这里,原理在之后工作过程中体会,慢慢了解再更新了。