首页 / MSSQL / SQL Server中使用PIVOT行转列

SQL Server中使用PIVOT行转列

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server中使用PIVOT行转列，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5025字，纯文字阅读大概需要8分钟。

内容图文

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

5.使用UNPIVOT列转行(待续)

 

原文：http://www.cnblogs.com/makesense/p/6124282.html

内容总结

以上是互联网集市为您收集整理的SQL Server中使用PIVOT行转列全部内容，希望文章能够帮你解决SQL Server中使用PIVOT行转列所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1059409.html

来源：【匿名】