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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 官方解释:可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。

在许多的互联网项目当中,报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在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/*行转列后 列的值*/) 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 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

小王 65

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1天前
|
SQL 数据库
数据库数据恢复—sqlserver数据库分区空间不足导致故障的数据恢复案例
数据库数据恢复环境: 某品牌r520服务器,服务器中有7块SAS硬盘,这7块硬盘组建了一组2盘raid1阵列和一组5盘raid5阵列,raid1阵列存储空间安装操作系统,raid5阵列存储空间存放数据。服务器上部署sql server数据库,数据库存放在C盘。 数据库故障: 工作人员发现服务器的C盘容量即将耗尽,于是将sql server数据库路径指向D盘,在D盘生成了一个.ndf文件。一个多星期后,sql server数据库出现故障,连接失效,无法正常附加查询。
数据库数据恢复—sqlserver数据库分区空间不足导致故障的数据恢复案例
|
7天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
16 0
|
8天前
|
存储 数据库
sqlserver------数据库的存储过程(练习)
sqlserver------数据库的存储过程(练习)
10 1
|
8天前
|
数据库
sqlserver数据库学习感悟(1)----关于group by
sqlserver数据库学习感悟(1)----关于group by
13 0
|
8天前
|
SQL Oracle 关系型数据库
数据库sqlserver-----触发器的插入,更新和删除
数据库sqlserver-----触发器的插入,更新和删除
16 3
|
8天前
|
SQL 关系型数据库 数据管理
Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
【5月更文挑战第14天】Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
18 2
|
8天前
|
SQL 存储 数据库连接
C#SQL Server数据库基本操作(增、删、改、查)
C#SQL Server数据库基本操作(增、删、改、查)
12 0
|
6天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
82 0
|
8天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
65 0
|
8天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
47 0