SQL Server中使用PIVOT行转列-白红宇

强烈建议你试试无所不能的chatGPT，快点击我

SQL Server中使用PIVOT行转列

阅读量：7060 次

发布时间：2019-06-28

本文共 7276 字，大约阅读时间需要 24 分钟。

使用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 GO12 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 GO10 11 CREATE procedure [dbo].[CaseSelect] AS12 13 BEGIN14 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]) Average21     FROM [dbo].[score] GROUP BY [Name] ORDER BY [Name] DESC22     23 END24 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 GO10 11 CREATE procedure [dbo].[Pivot]12     @NumberOfStudents int = 513 AS14 15 IF @NumberOfStudents < 1 or @NumberOfStudents > 1016     RAISERROR('@NumberOfStudents must be between 1 and 10', 11, 1);17 ELSE18     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 Average25     FROM [dbo].[score] PIVOT (SUM (score) FOR [subject] IN (English,linguistic,Mathematics,Other) ) AS P26     ORDER BY  p.[name] DESC27     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 GO10 11 CREATE procedure [dbo].[Pivot_DynamicColumn] AS12 13 BEGIN14     DECLARE @ColumnNames NVARCHAR(Max)15     DECLARE @AverageScore NVARCHAR(Max)16     DECLARE @ColumnCount int17  18     SET @ColumnNames=''19     SET @AverageScore = ''20     SET @ColumnCount = ''21     22     SELECT @ColumnCount = COUNT(DISTINCT [Subject]) FROM [Score]23  24     SELECT25        @ColumnNames = @ColumnNames + '[' + [Subject] + '],',26        @AverageScore = @AverageScore + '[' + [Subject] + ']+'27     FROM28        (29        SELECT DISTINCT [Subject] FROM [Score]30        ) t31  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 FROM39        [dbo].[score]40      Pivot(SUM(score) For [subject] in ({0})) AS p41        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 @selectSQL48 END49 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 GO10 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 GO18 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 nupvtGO

View Code

转载于:https://www.cnblogs.com/makesense/p/6124282.html

你可能感兴趣的文章

bzoj 2818: Gcd GCD(a,b) = 素数

Django实例(3)-用户连数据库登入系统

Tomcat的下载，安装，配置，案例，详解（不断补充中）

sql server 2000系统表sysproperties在SQL 2008中无效的问题

CMD一键获取cpu信息

SQLServer备份脚本

自定义函数实现位操作

二叉树的下一个结点

baidu map JSAPI

n的二进制中1的个数

MySQL-临时表、复制表、元数据

使用PopupWindow和Activity两种不同的方式实现弹窗效果

nodejs进阶(7)—async异步流程控制

轻量级的惰性控件——ViewStub

使用sed修改配置项的值

iOS局部刷新

[最新版]MJRefresh解析与详细使用指导

Android 常用代码大集合 [转]

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！

当前时间: 2025-02-07 15:07:57 当前IP: 18.188.22.126 联系邮箱:javaeecc@qq.com Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2

强烈建议你试试无所不能的CHAT-GPT，快点击我

强烈建议你试试无所不能的CHAT-GPT，快点击我