SQL Server中使用PIVOT行转列
1.建表及插入数据
1 USE [AdventureDB] 2 GO 3 /****** Object: Table [dbo].[Score] Script Date: 11/25/2016 4:30:50 PM ******/ 4 SET ANSI_NULLS ON 5 GO 6 7 SET QUOTED_IDENTIFIER ON 8 GO 9 10 CREATE TABLE [dbo].[Score]([Name] [varchar](50) NULL,[Subject] [varchar](50) NULL,[Score] FLOAT NULL) ON [PRIMARY] 11 GO 12 13 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'linguistic', 65) 14 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'linguistic', 56) 15 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'linguistic', 84) 16 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'Mathematics', 100) 17 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'Mathematics', 82) 18 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'Mathematics', 67) 19 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'English', 82) 20 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'English', 54) 21 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'English', 76) 22 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'Other', 52) 23 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Tom', N'Other', 99) 24 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Lina', N'Other', 79) 25 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'linguistic', 65) 26 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'linguistic', 76) 27 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'linguistic', 86) 28 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'Mathematics', 70) 29 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'Mathematics', 92) 30 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'Mathematics', 70) 31 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'English', 86) 32 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'English', 85) 33 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'James', N'English', 66) 34 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Jack', N'Other', 77) 35 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kobe', N'Other', 97) 36 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N'Kidd', N'Other', 93)View Code
2.使用CASE语句查询
1 USE [AdventureDB] 2 GO 3 4 /****** Object: StoredProcedure [dbo].[CaseSelect] Script Date: 12/02/2016 00:47:02 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 CREATE procedure [dbo].[CaseSelect] AS 12 13 BEGIN 14 15 SELECT [Name], 16 SUM (case when [Subject] = 'English' then [Score] else 0 end) English, 17 SUM (case when [Subject] = 'linguistic' then [Score] else 0 end) Linguistic, 18 SUM (case when [Subject] = 'Mathematics' then [Score] else 0 end) Mathematics, 19 SUM (case when [Subject] = 'Other' then [Score] else 0 end) Other, 20 AVG ([Score]) Average 21 FROM [dbo].[score] GROUP BY [Name] ORDER BY [Name] DESC 22 23 END 24 25 GOView Code
3.使用PIVOT行转列
1 USE [AdventureDB]
2 GO
3
4 /****** Object: StoredProcedure [dbo].[Pivot] Script Date: 12/02/2016 01:07:27 ******/
5 SET ANSI_NULLS ON
6 GO
7
8 SET QUOTED_IDENTIFIER ON
9 GO
10
11 CREATE procedure [dbo].[Pivot]
12 @NumberOfStudents int = 5
13 AS
14
15 IF @NumberOfStudents < 1 or @NumberOfStudents > 10
16 RAISERROR('@NumberOfStudents must be between 1 and 10', 11, 1);
17 ELSE
18 SELECT top(@NumberOfStudents)
19 p.[name],
20 p.English,
21 p.linguistic,
22 p.Mathematics,
23 p.Other,
24 (p.English + p.linguistic+p.Mathematics + p.Other)/4 AS Average
25 FROM [dbo].[score] PIVOT (SUM (score) FOR [subject] IN (English,linguistic,Mathematics,Other) ) AS P
26 ORDER BY p.[name] DESC
27
28 RETURN;
29
30 GO View Code4.PIVOT动态获取列
1 USE [AdventureDB]
2 GO
3
4 /****** Object: StoredProcedure [dbo].[Pivot_DynamicColumn] Script Date: 12/02/2016 01:31:30 ******/
5 SET ANSI_NULLS ON
6 GO
7
8 SET QUOTED_IDENTIFIER ON
9 GO
10
11 CREATE procedure [dbo].[Pivot_DynamicColumn] AS
12
13 BEGIN
14 DECLARE @ColumnNames NVARCHAR(Max)
15 DECLARE @AverageScore NVARCHAR(Max)
16 DECLARE @ColumnCount int
17
18 SET @ColumnNames=''
19 SET @AverageScore = ''
20 SET @ColumnCount = ''
21
22 SELECT @ColumnCount = COUNT(DISTINCT [Subject]) FROM [Score]
23
24 SELECT
25 @ColumnNames = @ColumnNames + '[' + [Subject] + '],',
26 @AverageScore = @AverageScore + '[' + [Subject] + ']+'
27 FROM
28 (
29 SELECT DISTINCT [Subject] FROM [Score]
30 ) t
31
32 SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)
33 SET @AverageScore= LEFT(@AverageScore, LEN(@AverageScore)-1)
34
35 DECLARE @selectSQL NVARCHAR(Max)
36
37 SET @selectSQL=
38 'SELECT [name],{0},({1})/{2} as Average FROM
39 [dbo].[score]
40 Pivot(SUM(score) For [subject] in ({0})) AS p
41 ORDER BY p.[name] DESC'
42
43 SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames)
44 SET @selectSQL= REPLACE(@selectSQL,'{1}',@AverageScore)
45 SET @selectSQL= REPLACE(@selectSQL,'{2}',@ColumnCount)
46
47 EXEC sp_executesql @selectSQL
48 END
49
50 GO View Code5.使用UNPIVOT列转行(待续)