数据库:SQLServer 实现行转列、列转行用法笔记

数据库:SQLServer 实现行转列、列转行用法笔记



数据库:SQLServer 实现行转列、列转行用法笔记

在许多的互联网项目当中,报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在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 '小王', '语文', 100INSERT INTO ScoreStatistics SELECT '小王', '数学', 90.5INSERT INTO ScoreStatistics SELECT '小王', '英语', 88INSERT INTO ScoreStatistics SELECT '小王', '历史', 65INSERT INTO ScoreStatistics SELECT '小李', '语文', 81INSERT INTO ScoreStatistics SELECT '小李', '数学', 99INSERT INTO ScoreStatistics SELECT '小李', '英语', 95INSERT INTO ScoreStatistics SELECT '小李', '历史', 90INSERT INTO ScoreStatistics SELECT '小刘', '语文', 90INSERT INTO ScoreStatistics SELECT '小刘', '数学', 85INSERT INTO ScoreStatistics SELECT '小刘', '英语', 59INSERT 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 ScoreStatisticsgroup by UserName-- PIVOT 写法更简洁SELECT * FROM ScoreStatisticsAS PPIVOT( SUM(Score/*行转列后 列的值*/) FOR p.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 pvtORDER BY pvt.[UserName]'PRINT (@sql_str);--打印执行的sqlEXEC (@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
小王 65

IT技术分享社区

个人博客网站:https://programmerblog.xyz

数据库:SQLServer 实现行转列、列转行用法笔记



文章推荐
SQL常用语句大全(值得收藏)
办公技巧:常用的100个Word快捷键!
GitHub上值得收藏的100个精选前端项目!
数据库优化:SQL 查找是否”存在”,别再 count 了,很耗费时间的!
学习 MySQL 高性能优化原理,这一篇就够了!
MySQL优化:数据量很大,分页查询很慢,有什么优化方案?
数据库:MySQL中,当update修改数据与原数据相同时会再次执行吗?



版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/2502.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • bootstrap 合并单元格之mergeCells属性合并

    bootstrap 合并单元格之mergeCells属性合并bootstrap合并单元格之mergeCells属性合并合并单元格有多种实现方式本文是根据bootstrap自带的mergeCells属性实现的单元格合并,原理是根据有规律的排序数据然后在展示层根据数据行数合并,这样的劣势是数据一定要有规律事先要排序还有一种是比较灵活的是事先处理好数据合并成一行,然后自己去展示层设计怎么展示,而且不用考虑分页问题,详情在我的…

  • 鸢尾花数据集knn算法可视化(在R中找到鸢尾花数据)

    kNN处理鸢尾花数据集kNN(KNearestNeighbor)算法是机器学习中最基础入门,也是最常用的算法之一,可以解决大多数分类与回归问题。这里以鸢尾花数据集为例,讨论分类问题中的kNN的思想。鸢尾花数据集内包含3类共150条记录,每类各50个数据,每条记录都有4项特征:花萼长度(sepallength)、花萼宽度(sepalwidth)、花瓣长度(petal…

  • 初识.Net审计

    初识.Net审计前言对.net认知比较少,学习一下.net的一些简单审计。遇到.net源码能简单审审。基础概念文件类型ASPX.cs是页面后的代码,aspx负责显示,服务器端的动作就是在as

    2021年12月13日
  • Landsat8 OLI数据不同波段组合作用

    Landsat8 OLI数据不同波段组合作用OLI波段合成 R、G、B 主要用途 4、3、2 自然真彩色 7、6、4 城市 5、4、3 标准假彩色图像、植被 6、5、2 农业 7、6、5 穿透大气层 5、6、2 健康植被 5、6、4

  • Udp攻击_dns是udp协议还是tcp协议

    Udp攻击_dns是udp协议还是tcp协议UDP洪流攻击是导致基于主机的服务拒绝攻击的一种。用户数据报协议(UDP)是一种无连接协议,它不需要用任何程序建立连接来传输数据。当数据包经由UDP协议发送时,发送双方无需通过三次握手建立连接,接收方必须接收处理该资料包。因此大量的发往受害主机UDP报文能使网络饱和。…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号