sqlserver行转列和列转行

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介: sqlserver行转列和列转行

一、行转列

原理

SQL Server中的行转列操作是通过使用PIVOT和UNPIVOT关系运算符来完成的

PIVOT操作符将表值表达式中的某一列中的唯一值转换为输出中的多个列,并在必要时对最终输出中所需的任何其余列值执行聚合。这使得多行的数据可以按照指定的列名进行重新分组,并且每行数据的每个列都可以进行汇总统计。使用PIVOT操作符进行行转列的基本语法如下:

SELECT <非透视的列>,  
       [第一个透视的列] AS <列名称>,  
       [第二个透视的列] AS <列名称>,  
       ...  
       [最后一个透视的列] AS <列名称>  
FROM (<生成数据的 SELECT 查询>) AS <源查询的别名>  
PIVOT ( <聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列], [第二个透视的列], ... [最后一个透视的列]) ) AS <透视表的别名>  
<可选的 ORDER BY 子句>;

UNPIVOT操作符则与PIVOT操作相反,它将表值表达式的列转换为列值。这使得在行被合并后,可以在结果中展示原始表值表达式的多行数据。使用UNPIVOT操作符进行行转列的基本语法如下:

SELECT [KeyColumn], [ValueColumn]  
FROM  
(  
  SELECT [ID], [Column1], [Column2], [Column3]  
  FROM [YourTable]  
) t  
UNPIVOT  
(  
  [Value] FOR [Column] IN ([Column1], [Column2], [Column3])  
) u;

需要注意的是,UNPIVOT操作并不完全是PIVOT的反向操作。虽然可以将行旋转为列,但是UNPIVOT不会像PIVOT那样执行聚合操作,而是将多行数据展示为多列数据。在UNPIVOT操作中,输入中的NULL值在输出中也会消失,这意味着如果原始数据中存在NULL值,那么这些NULL值在执行UNPIVOT操作后将不再出现在输出结果中。

探索

在SQL Server中,将行数据转换为列数据可以使用多种方法。以下是两种常用的方法:

方法一:使用PIVOT操作符

SELECT   
    [Column1], [Column2], [Column3]  
FROM   
    (SELECT   
        [RowID], [ColumnName], [ColumnValue]  
    FROM   
        [YourTable]) AS SourceTable  
PIVOT  
(  
    MAX([ColumnValue])  
    FOR [ColumnName] IN ([Column1], [Column2], [Column3])  
) AS PivotTable;

在上面的代码中,将[YourTable]替换为实际的表名。[RowID]是源表中的行标识符列,[ColumnName]是要转置的列名,[ColumnValue]是要转置的值。在PIVOT操作符中,使用MAX([ColumnValue])聚合函数将值转置为新的列。FOR [ColumnName] IN ([Column1], [Column2], [Column3])指定了要转置的列名。

方法二:使用CASE语句和聚合函数

SELECT   
    MAX(CASE WHEN [ColumnName] = 'Column1' THEN [ColumnValue] END) AS [Column1],  
    MAX(CASE WHEN [ColumnName] = 'Column2' THEN [ColumnValue] END) AS [Column2],  
    MAX(CASE WHEN [ColumnName] = 'Column3' THEN [ColumnValue] END) AS [Column3]  
FROM   
    [YourTable];

在上面的代码中,将[YourTable]替换为实际的表名。[ColumnName]是要转置的列名,[ColumnValue]是要转置的值。使用CASE语句和聚合函数将值转置为新的列。

这些方法中的每一个都有其特定的用途和限制

范例

以下是使用PIVOT操作符将行数据转换为列数据的示例:

假设我们有一个名为Sales的表,其中包含以下列:SalesID、Product和QuantitySold。该表用于记录每个销售产品的数量。

表结构如下:

SalesID Product QuantitySold
1 A 10
2 B 5
3 C 3
4 A 8
5 B 2
6 C 7
现在,我们希望将每个产品的销售数量按照产品类别进行转置,以便更容易进行分析和比较。以下是使用PIVOT操作符完成此任务的示例代码
SELECT *  
FROM  
(  
  SELECT SalesID, Product, QuantitySold  
  FROM Sales  
) AS SourceTable  
PIVOT  
(  
  SUM(QuantitySold)  
  FOR Product IN ([A], [B], [C]) -- 这里列出所有可能的产品类别  
) AS PivotTable;

执行上述代码后,将返回以下结果:

A B C

18 7 10

上述结果将每个产品的销售数量转置为单独的列,使得我们可以更轻松地对不同产品的销售数据进行比较和分析。

实战

行转列

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

go

CREATE TABLE tb(姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)

insert into tb VALUES ('张三','语文',74)
insert into tb VALUES ('张三','数学',83)
insert into tb VALUES ('张三','物理',93)
insert into tb VALUES ('李四','语文',74)
insert into tb VALUES ('李四','数学',84)
insert into tb VALUES ('李四','物理',94)

go

SELECT * FROM tb

go



SELECT * FROM tb pivot( MAX(分数) FOR 课程 IN (语文,数学,物理))a

列转行

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
go

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)

INSERT INTO tb VALUES('张三',74,83,93)

INSERT INTO tb VALUES('李四',74,84,94)

go

SELECT * FROM tb
--SQL SERVER 2005动态SQL

SELECT  姓名 ,
        课程 ,
        分数
FROM    tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t

二、列转行

在SQL Server中,将列转换为行通常涉及使用UNPIVOT操作符。UNPIVOT允许你将列转换为行。以下是使用UNPIVOT的基本语法

SELECT [KeyColumn], [ValueColumn]  
FROM  
(  
  SELECT [ID], [Column1], [Column2], [Column3]  
  FROM [YourTable]  
) t  
UNPIVOT  
(  
  [Value] FOR [Column] IN ([Column1], [Column2], [Column3])  
) u;

在上面的语法中,[YourTable]是你要进行转换的表名,[ID]是可选的键列,[Column1], [Column2], [Column3]是要进行转换的列。这个查询将返回一个包含键列和值列的结果集,其中值列包含从列转换为行的值。

以下是一个具体的示例:

-- 创建一个示例表  
CREATE TABLE MyTable (ID INT, Column1 VARCHAR(10), Column2 VARCHAR(10), Column3 VARCHAR(10));  
  
-- 插入一些数据  
INSERT INTO MyTable (ID, Column1, Column2, Column3)  
VALUES (1, 'A', 'B', 'C'), (2, 'D', 'E', 'F'), (3, 'G', 'H', 'I');  
  
-- 使用UNPIVOT将列转换为行  
SELECT ID, Column, Value  
FROM  
(  
  SELECT ID, Column1, Column2, Column3  
  FROM MyTable  
) t  
UNPIVOT  
(  
  Value FOR Column IN (Column1, Column2, Column3)  
) u;

在上面的示例中,我们创建了一个名为MyTable的表,其中包含ID、Column1、Column2和Column3列。然后,我们使用UNPIVOT将列转换为行,并将结果按ID、Column和Value列返回。

相关实践学习
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
目录
相关文章
|
BI 数据库
数据库:SQLServer 实现行转列、列转行用法笔记
官方解释:可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。
数据库:SQLServer 实现行转列、列转行用法笔记
|
SQL 存储 数据库
重温SQL Server的行转列和列转行,面试常考题
行转列,列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 的运算符PIVOT来实现。用传统的方法,比较好理解。层次清晰,而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。
重温SQL Server的行转列和列转行,面试常考题
|
SQL BI 前端开发
做图表统计你需要掌握SQL Server 行转列和列转行
原文:做图表统计你需要掌握SQL Server 行转列和列转行 说在前面            做一个数据统计和分析的项目,每天面对着各种数据,经过存储过程从源表计算汇总后需要写入中间结果表以提高数据使用效率,那么此时就需要用到行转列和列转行。
2074 0
|
27天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL 存储 安全
sql server 数据库实例
SQL Server 数据库实例是指在 SQL Server 中创建的一个独立的数据库环境。每个数据库实例都拥有自己的一套完整的数据库文件、配置设置、用户和权限等,可以独立地进行管理和操作。以下是关于
|
1月前
|
SQL 监控 安全
sql server数据库监控
SQL Server数据库监控是收集、聚合和监控SQL服务器的各种指标的过程,旨在维护SQL数据库的运行状况和可用性,优化性能,并预防或快速响应错误。以下是一些关于SQL Server数据库监控的核心
|
5天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
11天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
21 2
|
17天前
|
SQL 弹性计算 数据管理
数据管理DMS产品使用合集之sql server实例,已经创建了数据库,登录时提示实例已存在,该怎么处理
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
29 1