SQL Server中使用PIVOT行转列

使用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 GO

View 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 Code

4.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 Code

使用UNPIVOT列转行

1.建表及插入数据

 1 USE [AdventureDB]
 2 GO
 3 
 4 /****** Object:  Table [dbo].[ScorePivot]    Script Date: 2016/12/6 17:38:48 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 CREATE TABLE [dbo].[ScorePivot](
12     [Name] [varchar](50) NULL,
13     [English] [varchar](50) NULL,
14     [Linguistic] [varchar](50) NULL,
15     [Mathematics] [varchar](50) NULL,
16     [Other] [varchar](50) NULL) ON [PRIMARY]
17 GO
18 
19 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Tom', N'54', N'56', N'82', N'99')
20 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Lina', N'76', N'84', N'67', N'79')
21 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Kobe', N'86', N'65', N'92', N'97')
22 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Kidd', N'85', N'86', N'70', N'93')
23 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'James', N'66', N'76', N'70', N'52')
24 INSERT INTO [dbo].[ScorePivot] ([Name], [English], [Linguistic], [Mathematics], [Other]) VALUES (N'Jack', N'82', N'65', N'100', N'77')

View Code

2.使用UNPIVOT列转行

USE [AdventureDB]
GO

/****** Object:  StoredProcedure [dbo].[UNPivot]    Script Date: 2016/12/6 17:49:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[UNPivot]  AS

    SELECT
        [Name], [Subject], [Score]
    FROM 
    (
        SELECT [Name], [English],[Linguistic],[Mathematics], [Other] FROM [dbo].[ScorePivot]
    ) data
    UNPIVOT
    (
        [Score] FOR [Subject] IN 
        (
            [English], [Linguistic], [Mathematics], [Other]
        ) 
    )AS nupvt

GO

View Code