聊城手机站网站公司电话,电子商务网站建设课后答案,网页设计代码案例,h5个人简历模板在许多的互联网项目当中#xff0c;报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在SQLServer当中如何使用PIVOT、UNPIVOT内置函数实现数据报表的行转列、列转行。有需要的朋友可以一起学习… 在许多的互联网项目当中报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在SQLServer当中如何使用PIVOT、UNPIVOT内置函数实现数据报表的行转列、列转行。有需要的朋友可以一起学习一下。一、PIVOT、UNPIVOT用途官方解释可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作将表值表达式的列转换为列值。注意UNPIVOT运算符通过将列旋转到行来执行PIVOT的反向操作UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 执行聚合并将多个可能的行合并为输出中的一行。UNPIVOT 不重现原始表值表达式的结果因为行已被合并。另外UNPIVOT 输入中的 NULL 值也在输出中消失了。如果值消失表明在执行 PIVOT 操作前输入中可能就已存在原始 NULL 值。二、PIVOT语法格式SELECT 非透视的列, [第一个透视的列] AS 列名称, [第二个透视的列] AS 列名称, ... [最后一个透视的列] AS 列名称,FROM (生成数据的 SELECT 查询) AS 源查询的别名PIVOT( 聚合函数(要聚合的列)FOR[包含要成为列标题的值的列] IN ( [第一个透视的列], [第二个透视的列], ... [最后一个透视的列])) AS 透视表的别名可选的 ORDER BY 子句;三、行转列示例说明-- 创建测试表 学习成绩统计表
CREATE TABLE ScoreStatistics
(UserName NVARCHAR(20), --学生姓名SubjectName NVARCHAR(30), --科目名称Score FLOAT, --成绩
)
-- 插入测试数据
INSERT INTO ScoreStatistics SELECT 小王, 语文, 100
INSERT INTO ScoreStatistics SELECT 小王, 数学, 90.5
INSERT INTO ScoreStatistics SELECT 小王, 英语, 88
INSERT INTO ScoreStatistics SELECT 小王, 历史, 65
INSERT INTO ScoreStatistics SELECT 小李, 语文, 81
INSERT INTO ScoreStatistics SELECT 小李, 数学, 99
INSERT INTO ScoreStatistics SELECT 小李, 英语, 95
INSERT INTO ScoreStatistics SELECT 小李, 历史, 90
INSERT INTO ScoreStatistics SELECT 小刘, 语文, 90
INSERT INTO ScoreStatistics SELECT 小刘, 数学, 85
INSERT INTO ScoreStatistics SELECT 小刘, 英语, 59
INSERT INTO ScoreStatistics SELECT 小刘, 历史, 98
-- 传统写法
select UserName,max(case SubjectName when 语文 then Score else 0 end)语文,max(case SubjectName when 数学then Score else 0 end)数学,max(case SubjectName when 英语then Score else 0 end)英语,max(case SubjectName when 历史then Score else 0 end)历史
from ScoreStatistics
group by UserName
-- PIVOT 写法更简洁
SELECT * FROM ScoreStatistics
AS P
PIVOT
(SUM(Score/*行转列后 列的值*/) FORp.SubjectName/*需要行转列的列*/ IN ([语文],[数学],[英语],历史/*列的值*/)
) AS T
-- order by 语文 desc 具体科目排序
-- order by username desc -- 姓名排序
-- 动态拼接列的示例
DECLARE sql_str VARCHAR(8000); -- 要执行的sql
--拿到数值列 [历史],[数学],[英语],[语文]
DECLARE sql_col VARCHAR(8000);
SELECT sql_col ISNULL(sql_col ,,) QUOTENAME(SubjectName)
FROM ScoreStatistics GROUP BY SubjectName;
print(sql_col); -- 打印数值列,不必需
SET sql_str
SELECT * FROM (
SELECT [UserName],[SubjectName],[Score] FROM [ScoreStatistics])
p PIVOT
(SUM([Score]) FOR [SubjectName] IN ( sql_col ) ) AS pvt
ORDER BY pvt.[UserName]
PRINT (sql_str);--打印执行的sql
EXEC (sql_str);-- 执行查询
输出结果UserName 语文 数学 英语 历史小王 100 90.5 88 65小刘 90 85 59 98小李 81 99 95 90四、列转行示例-- 插入测试表CREATE TABLE ScoreSummary( UserName NVARCHAR(20), --学生姓名 数学 FLOAT, --数学成绩 英语 FLOAT, --英语成绩 语文 FLOAT, --语文成绩 历史 FLOAT, --历史成绩)-- 插入测试数据INSERT INTO ScoreSummary SELECT 小李,81,99,95,90;INSERT INTO ScoreSummary SELECT 小刘,90,85,59,98;INSERT INTO ScoreSummary SELECT 小王,100,90.5,88,65;-- 查询用法select aa.UserName,aa.Scorefrom (select UserName,数学,英语,语文,历史 from dbo.ScoreSummary) as aunpivot(Score for ScoreSummary in(数学,英语,语文,历史)) as aa order by aa.UserName
输出结果
UserName Score小李 81小李 99小李 95小李 90小刘 90小刘 85小刘 59小刘 98小王 100小王 90.5小王 88小王 65IT技术分享社区个人博客网站https://programmerblog.xyz 文章推荐 SQL常用语句大全值得收藏 办公技巧常用的100个Word快捷键 GitHub上值得收藏的100个精选前端项目 数据库优化SQL 查找是否存在别再 count 了很耗费时间的 学习 MySQL 高性能优化原理这一篇就够了 MySQL优化数据量很大分页查询很慢有什么优化方案 数据库MySQL中当update修改数据与原数据相同时会再次执行吗
