SQL Server中使用PIVOT行转列
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server中使用PIVOT行转列,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5918字,纯文字阅读大概需要9分钟。
内容图文
![SQL Server中使用PIVOT行转列](/upload/InfoBanner/zyjiaocheng/473/5303f9f60d6144c99a23a16046d6af81.jpg)
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 GOView 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 GOView Code
5.使用UNPIVOT列转行(待续)
SQL Server中使用PIVOT行转列
标签:rom subject splay log color art char arc pivot
本文系统来源:http://www.cnblogs.com/makesense/p/6124282.html
内容总结
以上是互联网集市为您收集整理的SQL Server中使用PIVOT行转列全部内容,希望文章能够帮你解决SQL Server中使用PIVOT行转列所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。