SQL Server中使用PIVOT行转列

                 1
                USE
                [
                AdventureDB
                ]
                 2
                GO
                 3
                /*
                ***** Object:  Table [dbo].[Score]    Script Date: 11/25/2016 4:30:50 PM *****
                */
                 4
                SET ANSI_NULLS ON 5GO 6 7SET QUOTED_IDENTIFIER ON 8GO 910CREATETABLE[dbo].[Score]([Name][varchar](50) NULL,[Subject][varchar](50) NULL,[Score]FLOATNULL) ON[PRIMARY]11GO1213INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Jack‘, N‘linguistic‘, 65)
14INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Tom‘, N‘linguistic‘, 56)
15INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Lina‘, N‘linguistic‘, 84)
16INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Jack‘, N‘Mathematics‘, 100)
17INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Tom‘, N‘Mathematics‘, 82)
18INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Lina‘, N‘Mathematics‘, 67)
19INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Jack‘, N‘English‘, 82)
20INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Tom‘, N‘English‘, 54)
21INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Lina‘, N‘English‘, 76)
22INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘James‘, N‘Other‘, 52)
23INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Tom‘, N‘Other‘, 99)
24INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Lina‘, N‘Other‘, 79)
25INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kobe‘, N‘linguistic‘, 65)
26INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘James‘, N‘linguistic‘, 76)
27INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kidd‘, N‘linguistic‘, 86)
28INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘James‘, N‘Mathematics‘, 70)
29INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kobe‘, N‘Mathematics‘, 92)
30INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kidd‘, N‘Mathematics‘, 70)
31INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kobe‘, N‘English‘, 86)
32INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kidd‘, N‘English‘, 85)
33INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘James‘, N‘English‘, 66)
34INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Jack‘, N‘Other‘, 77)
35INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kobe‘, N‘Other‘, 97)
36INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Kidd‘, N‘Other‘, 93)

                 1
                USE
                [
                AdventureDB
                ]
                 2
                GO
                 3
                 4
                /*
                ***** Object:  StoredProcedure [dbo].[CaseSelect]    Script Date: 12/02/2016 00:47:02 *****
                */
                 5
                SET ANSI_NULLS ON 6GO 7 8SET QUOTED_IDENTIFIER ON 9GO1011CREATEprocedure[dbo].[CaseSelect]AS1213BEGIN1415SELECT[Name],
16SUM (casewhen[Subject]=‘English‘then[Score]else0end) English,
17SUM (casewhen[Subject]=‘linguistic‘then[Score]else0end) Linguistic,
18SUM (casewhen[Subject]=‘Mathematics‘then[Score]else0end) Mathematics,
19SUM (casewhen[Subject]=‘Other‘then[Score]else0end) Other,
20AVG ([Score]) Average
21FROM[dbo].[score]GROUPBY[Name]ORDERBY[Name]DESC2223END2425GO

                 1
                USE
                [
                AdventureDB
                ]
                 2
                GO
                 3
                 4
                /*
                ***** Object:  StoredProcedure [dbo].[Pivot]    Script Date: 12/02/2016 01:07:27 *****
                */
                 5
                SET ANSI_NULLS ON 6GO 7 8SET QUOTED_IDENTIFIER ON 9GO1011CREATEprocedure[dbo].[Pivot]12@NumberOfStudentsint=513AS1415IF@NumberOfStudents<1or@NumberOfStudents>1016RAISERROR(‘@NumberOfStudents must be between 1 and 10‘, 11, 1);
17ELSE18SELECTtop(@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)/4AS Average
25FROM[dbo].[score] PIVOT (SUM (score) FOR[subject]IN (English,linguistic,Mathematics,Other) ) AS P
26ORDERBY  p.[name]DESC2728RETURN;
2930GO

                 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 6GO 7 8SET QUOTED_IDENTIFIER ON 9GO1011CREATEprocedure[dbo].[Pivot_DynamicColumn]AS1213BEGIN14DECLARE@ColumnNamesNVARCHAR(Max)
15DECLARE@AverageScoreNVARCHAR(Max)
16DECLARE@ColumnCountint1718SET@ColumnNames=‘‘19SET@AverageScore=‘‘20SET@ColumnCount=‘‘2122SELECT@ColumnCount=COUNT(DISTINCT[Subject]) FROM[Score]2324SELECT25@ColumnNames=@ColumnNames+‘[‘+[Subject]+‘],‘,
26@AverageScore=@AverageScore+‘[‘+[Subject]+‘]+‘27FROM28       (
29SELECTDISTINCT[Subject]FROM[Score]30       ) t
3132SET@ColumnNames=LEFT(@ColumnNames, LEN(@ColumnNames)-1)
33SET@AverageScore=LEFT(@AverageScore, LEN(@AverageScore)-1)
3435DECLARE@selectSQLNVARCHAR(Max)
3637SET@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‘4243SET@selectSQL=REPLACE(@selectSQL,‘{0}‘,@ColumnNames)
44SET@selectSQL=REPLACE(@selectSQL,‘{1}‘,@AverageScore)
45SET@selectSQL=REPLACE(@selectSQL,‘{2}‘,@ColumnCount)
4647EXEC sp_executesql @selectSQL48END4950GO