深圳市住房和建设网站,东莞计算机培训机构哪个最好,网站建设验收标准,通信设计是干什么的PIVOT函数常用于数据的行转列#xff0c;同时也可以用此函数实现类似于Excel中的数据透视表的效果。
PIVOT函数
PIVOT 函数的基本语法如下#xff1a;
-- PIVOT 语法
SELECT 非透视的列,[第一个透视的列] AS 列名称,[第二个透视的列] AS 列名称,.…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值
