SQL Povit函数使用及实例

SQL Povit函数使用及实例PIVOT 函数用法与实例 sqlpivot

大家好,欢迎来到IT知识分享网。

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函数来生成

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://download.csdn.net/download/_/
在这里插入图片描述

免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/145304.html

(0)
上一篇 2024-11-24 12:15
下一篇 2024-11-24 12:26

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注微信