SQL Povit函数使用及实例

这篇具有很好参考价值的文章主要介绍了SQL Povit函数使用及实例。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

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  

生成后的表结构如下:
SQL Povit函数使用及实例,SQL,sql,数据库,oracle
现在如果需要降此数据进行分类汇总,得到每个人对应类别的价格加总,有如下两种方法:
第一种方法是使用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函数来生成

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

两个语句生成的结果是一致的
SQL Povit函数使用及实例,SQL,sql,数据库,oracle
注意: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(必须是当前数据库版本范围内的兼容级别)
不同数据库版本支持的兼容级别如下:
SQL Povit函数使用及实例,SQL,sql,数据库,oracle

动态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值
SQL Povit函数使用及实例,SQL,sql,数据库,oracle

代码下载:https://download.csdn.net/download/qq_42692386/88757394
SQL Povit函数使用及实例,SQL,sql,数据库,oracle文章来源地址https://www.toymoban.com/news/detail-805320.html

到了这里,关于SQL Povit函数使用及实例的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • SQL SERVER数据库修复之REPAIR_ALLOW_DATA_LOSS级别简介和实例

    目录 (一)前言 (二)关于DBCC CHECKxxx系列命令 1. DBCC CHECKALLOC 2. DBCC CHECKCATALOG 3. DBCC CHECKDB (1)基本概念 (2)基本语法 (3)参数说明 (三)实际操作案例 1. 将数据库状态改为紧急模式 2. 将数据库改为单用户访问 3. 运行repair_allow_data_loss修复 4. 修复完成后运行DBCC CH

    2024年02月04日
    浏览(40)
  • SQL Server判断数据库、表、列、视图、存储过程、函数是否存在

    在写一些业务逻辑相对复杂点的存储过程的时候,经常会用到临时表或者数据表作为临时结果的保存。但每次在作表是否存在的判断时,往往想不起完整的SQL写法。因此,记录一些常用的数据库对象是否存在的判断方法,可以达到快速查找的目的。正是:好记性不如烂笔头。

    2024年02月02日
    浏览(110)
  • SQL Server数据库使用SQL Server代理实现数据库自动备份

    在现实中,为了保证数据的安全和完整,防止人为错误和硬件故障等造成的数据丢失和损坏,就需要用到数据库的备份,不同的数据库方法有所差别,我这边主讲SQL Server数据库的备份和使用SQL Server代理作业实现数据库的定时备份。 目录 一、开启SQL Server代理 1、找到SQL Serv

    2024年02月09日
    浏览(76)
  • 数据库SQL函数 根据身份证号/出生年月 精确计算年龄(Oracle/MySQL)

    问题 根据身份证号统计年龄(18位) Oracle 思路 (1)Substr()函数在Oracle使用中表示被劫取的字符串表达式,截取字符串的内容。 (2)To_date()函数可以转换不同格式的日期,通过使用to_date函数可以将字符串类型的日期转换成date格式。 (3)Months_between()函数反悔两个日期之间的

    2024年02月11日
    浏览(47)
  • 数据库SQL语句使用

    -- 查询所有数据库 -- 创建数据库,数据库名为mydatabase   -- 如果没有名为 mydatabase的数据库则创建,有就不创建   -- 如果没有名为 mydatabase的数据库则创建,有就不创建,数据库字符编码设置为utf8mb4 -- 使用数据库 -- 删除名为mydatabase的数据库 -- 如果有名为 mydatabase的数据库则

    2024年02月12日
    浏览(45)
  • SQL Server数据库使用

    SQL Server 2008 R2的安装和使用 安装前的准备 安装过程 1)安装所选功能 2)安装的类型:默认或命名实例 3)服务账户 4)身份验证模式 5)排序规则设置 6)开始安装 7)完成安装 验证数据库安装成功 【开始】|【程序】中可以看到Microsoft SQL Server 2008的程序组 启动和停止数据库

    2024年02月11日
    浏览(73)
  • 【数据库】日常使用PL/SQL 登录ORACLE 数据库查询数据

    一、PL/SQL 登录方式 username: ##访问数据库的账号 password: ##访问数据库的密码 Databse: ##数据库IP地址/实例名 数据库集群心跳地址/实例名 Connect as : ##Normal,如果使用sysdba账户登录选择SYSDBA 二、PL/SQL使用SQL语句查询 点击上方导航栏,New,选择SQL Window,即可再次输入要查询的

    2024年02月19日
    浏览(68)
  • 使用SQL语句创建数据库与创建表

    🎈个人主页:🎈 :✨✨✨初阶牛✨✨✨ 🐻推荐专栏: 🍔🍟🌯 c语言初阶 🔑个人信条: 🌵知行合一 🍉本篇简介:: 本篇记录一下牛牛在学校学习的sql serve数据库知识,内容比较基础适合,适合数据库初学者学习,希望对大家有些帮助,一起加油吧! 创建数据库模板: 还是范例看的更

    2024年02月03日
    浏览(56)
  • Microsoft SQL Server 数据库使用(三)

    ##Microsoft SQL Server 数据库使用(三) 一、查询数据 注:下面使用 Microsoft SQL Server 2019 数据库练习使用可以在我的资源下载中下载数据库脚本文件。 1.分页查询 (1)、OFFSET 子句指定在开始从查询返回行之前要跳过的行数。 offset_row_count 可以是大于或等于零的常量,变量或参数

    2024年02月06日
    浏览(46)
  • Microsoft SQL Server 数据库使用(一)

    一、Microsoft SQL Server安装 注:下面使用 Microsoft SQL Server 2019 数据库练习使用可以在我的资源下载中下载数据库脚本文件。 1.安装SQL准备工作 (1)、如果之前或当前电脑已经安装了Microsoft SQL Server更早版本的,请在安装之前把相应的“配置管理工具”进行端口号修改如果没有安

    2024年02月05日
    浏览(55)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包