SQL Povit函数使用及实例_F11 - 专业站长和开发者的学习网站

分享到

SQL Povit函数使用及实例

MsSql 来源：互联网作者：佚名发布时间：2024-07-18 18:54:47 人浏览

摘要

PIVOT函数常用于数据的行转列，同时也可以用此函数实现类似于Excel中的数据透视表的效果。 PIVOT函数 PIVOT 函数的基本语法如下： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 -- PIVOT 语法 SELECT 非透视的列

PIVOT函数常用于数据的行转列，同时也可以用此函数实现类似于Excel中的数据透视表的效果。

PIVOT函数

PIVOT 函数的基本语法如下：

-- PIVOT 语法

SELECT <非透视的列>,

[第一个透视的列] AS <列名称>,

[第二个透视的列] AS <列名称>,

...

[最后一个透视的列] AS <列名称>,

FROM

(<生成数据的 SELECT 查询>)

AS <源查询的别名>

PIVOT

(

<聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

IN ( [第一个透视的列], [第二个透视的列],

... [最后一个透视的列])

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

为了更好的解释Povit函数的使用，下面建立一张测试用的临时数据表：

CREATE TABLE #ShoppingCart( [Name] nvarchar(8) NOT NULL, [Category] nvarchar(8) NOT NULL, [TotalPrice] DECIMAL DEFAULT(0) NOT NULL )

INSERT INTO #ShoppingCart([Name],[Category],[TotalPrice])

SELECT '张三','饼干',30 UNION ALL

SELECT '张三','面包',10 UNION ALL

SELECT '张三','果冻',30 UNION ALL

SELECT '李四','饼干',40 UNION ALL

SELECT '李四','面包',20 UNION ALL

SELECT '李四','果冻',20 UNION ALL

SELECT '陈小二','饼干',20 UNION ALL

SELECT '陈小二','饼干',50 UNION ALL

SELECT '陈小二','面包',30 UNION ALL

SELECT '陈小二','果冻',30

生成后的表结构如下：

现在如果需要降此数据进行分类汇总，得到每个人对应类别的价格加总，有如下两种方法：
第一种方法是使用Case when语句计算：

SELECT [Name],

SUM(CASE [Category] WHEN '饼干' THEN [TotalPrice] ELSE 0 END) AS '饼干',

SUM(CASE [Category] WHEN '果冻' THEN [TotalPrice] ELSE 0 END) AS '果冻',

SUM(CASE [Category] WHEN '面包' THEN [TotalPrice] ELSE 0 END) AS '面包'

FROM [#ShoppingCart]

GROUP BY [Name]

第二种方法则是使用povit函数来生成

1	select * from #ShoppingCart pivot(sum([TotalPrice]) for [Category] in (饼干,果冻,面包)) a

两个语句生成的结果是一致的

注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需满足数据库的兼容级别，需要兼容级别大于90，否则计算出的结果可能会与上面的截图不同。
查询兼容级别语法如下：
SELECT compatibility_level FROM sys.databases WHERE name = '数据库名';

如果你是跟着上述代码生成的测试数据，则数据表是一张临时表，所以对应的数据库名是Tempdb,
SELECT compatibility_level FROM sys.databases WHERE name = 'Tempdb';

修改兼容级别语法如下：
ALTER DATABASE 数据库名 SET COMPATIBILITY_LEVEL = 90（必须是当前数据库版本范围内的兼容级别）
不同数据库版本支持的兼容级别如下：

动态PIVOT行转列

在日常的数据处理中经常会遇到要转化为列字段的数量特别多的情况，这时如果用上面的语句填写要填写非常多的字段，所以需要使用动态代码进行处理
···
–动态PIVOT行转列
DECLARE @sql_str VARCHAR(8000)
DECLARE @sql_col VARCHAR(8000)
SELECT @sql_col = ISNULL(@sql_col + ‘,’,‘’) + QUOTENAME([Category]) FROM #ShoppingCart GROUP BY [Category]–这一句用于选出所有的列标题并加上[]号，然后连接起来
SET @sql_str = ’
SELECT * FROM #ShoppingCart PIVOT
(SUM([TotalPrice]) FOR [Category] IN ( ‘+ @sql_col +’) ) AS pvt’
PRINT (@sql_str)
EXEC (@sql_str)
···

Unpovit函数

UNPIVOT函数,与上述功能相反,把列转成行。我们直接使用WITH关键字把上述PIVOT查询当成源表,然后再使用UNPIVOT关键把它旋转回原来的模样

WITH Pvt AS

(

select * from #ShoppingCart pivot(sum([TotalPrice]) for [Category] in ([饼干],[果冻],[面包])) as a

)

SELECT [Name],[Category],[TotalPrice]

FROM 　　Pvt

UNPIVOT( [TotalPrice] FOR [Category] in ([饼干],[果冻],[面包]) )AS T

结果如下，可以看到如果用PIVOT 执行聚合，并将多行合并为输出中的一行，UNPIVOT就只能输出合并后的一行数据了。另外PIVOT函数也不会输出值为Null的数据行，所以UNPIVOT 同样也无法复原原始数据中原有的null值

代码下载：

百度网盘：

链接: https://pan.baidu.com/s/1FiQWnWb6xZ4f32HDSkcqwg?pwd=tyby

提取码: tyby

您可能感兴趣的文章 :

原文链接 :

Tag : sql(98)函数(84)

sql server安装及使用全流程

一、安装SQL Server 1.到微软官网下载SQL Server Developer版本，现在的最新版本是SQL Server 2019 Developer。微软官网传送门：点击此处直达 2.下载完成
SQL Povit函数使用及实例

PIVOT函数常用于数据的行转列，同时也可以用此函数实现类似于Excel中的数据透视表的效果。 PIVOT函数 PIVOT 函数的基本语法如下： 1 2 3 4 5
Navicat连接SQL server出现：[IM002] [Microsoft][ODBC 驱动

问题解决方法一找到Navicat的安装路径，然后找到sqlncli_x64.msi文件并安装，安装成功后重启Navicat重新进行连接，看是否成功。解决方法二
sql server实现图片的存入和读取的流程介绍

首先提醒一下，无论是将图片存入数据库，还是从数据库中读取，都是采用流的形式进行，虽然，在数据库中，存储图片的类型是Image，但是
Sql中的like的用法及示例操作介绍

Sql中的like的用法大家好，我是免费搭建查券返利机器人赚佣金就用微赚淘客系统3.0的小编，也是冬天不穿秋裤，天冷也要风度的程序猿！今
对SQL Server中敏感数据进行加密解密的操作方法

为什么需要对敏感数据进行加密？近几年有不少关于个人数据泄露的新闻（个人数据通常包含如姓名、地址、身份证号码、财务信息等），
SSMS中出现两个相同的服务器名称的问题解决

在将SQL Server 2019更新为SQL Server 2022后，或者是在更新安装SQL Server Management Studio工具后，在SSMS的【连接到服务器】页面，有时候可能会出现多
配置SQL Server2022远程访问的方法介绍

对于一个老菜鸟来说，配置的过程一波三折，费了九牛二虎之力，折腾了一上午才弄好，特此记录下来备用。由于工作需要，Hyper-V虚拟机上
SQL Server的彻底卸载方法

SQL Server的彻底卸载与再次安装可能大家已经有深刻体会，SQL Server的卸载十分繁琐。最让人头疼的是，出了问题的SQL Server未卸载完全就再次
SQLServer或Oracle卸载不完全导致安装失败的解决办

本文归根结底的办法就是利用cmd命令行将未卸载完全的插件或程序进行完美卸载(需要手动先卸载Oracle之后，执行cmd删除，最后删除注册表和