SQL SERVER【1】——行转列

本文介绍如何在SQL中实现行转列操作,包括使用CASE语句、PIVOT函数及动态SQL等多种方法,并通过实例展示了具体的实现过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

行列转换在编程中用的比较多,故稍微总结了下,具体看下面:

一.创建测试数据表

/*创建册数数据表【订单表】*/
USE [TEST]
GO

/****** Object:  Table [dbo].[订单表]    Script Date: 12/03/2012 16:56:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[订单表](
	[年度] [nchar](10) NULL,
	[月份] [int] NULL,
	[数量] [numeric](12, 0) NULL
) ON [PRIMARY]

GO
------------END------------------------------

--所有数据
select * from 订单表

数据如下图:

二.实现方法如下:

--**************静态SQL******************************
--1.行转列
--【1】CASE……WHEN……THEN……END
SELECT 年度,
SUM(CASE(月份) WHEN 1  THEN 数量 END)  "1月份",
SUM(CASE(月份) WHEN 2  THEN 数量 END)  "2月份",
SUM(CASE(月份) WHEN 3  THEN 数量 END)  "3月份",
SUM(CASE(月份) WHEN 4  THEN 数量 END)  "4月份",
SUM(CASE(月份) WHEN 5  THEN 数量 END)  "5月份",
SUM(CASE(月份) WHEN 6  THEN 数量 END)  "6月份",
SUM(CASE(月份) WHEN 7  THEN 数量 END)  "7月份",
SUM(CASE(月份) WHEN 8  THEN 数量 END)  "8月份",
SUM(CASE(月份) WHEN 9  THEN 数量 END)  "9月份",
SUM(CASE(月份) WHEN 10 THEN 数量 END)  "10月份",
SUM(CASE(月份) WHEN 11 THEN 数量 END)  "11月份",
SUM(CASE(月份) WHEN 12 THEN 数量 END)  "12月份"
FROM 订单表
GROUP BY 年度
ORDER BY 年度

结果如下:


 

以下几种SQL写法稍微不同,结果都一样。(有静态有动态)

--【2】使用pivot转换——SQL 2005以上的版本才有的
select * from 订单表 pivot (SUM(数量) 
for 月份 in("1","2","3","4","5","6","7","8","9","10","11","12"))a
order by 年度

--【3】使用动态SQL
--变量按sql语言顺序赋值
declare @sql varchar(8000)
set @sql=' SELECT 年度'
select @sql=@sql+',SUM(CASE CONVERT(VARCHAR(2),月份) WHEN '''+CONVERT(VARCHAR(2),月份)+''' THEN 数量 ELSE 0 END) ['+CONVERT(VARCHAR(2),月份)+']' 
from(SELECT DISTINCT 月份 FROM 订单表) as a   --同 from 订单表 group by  月份
set @sql=@sql+' FROM 订单表 GROUP BY 年度 ORDER BY 年度'
exec(@sql)
--使用isnull(),变量先确定动态部分
declare @sql varchar(8000)
select @sql=ISNULL(@sql+',','')+' SUM(CASE CAST(月份 AS VARCHAR(2)) WHEN '''+CAST(月份 AS VARCHAR(2))+''' THEN 数量 ELSE 0 END) ['+CAST(月份 AS VARCHAR(2))+']'
from(SELECT DISTINCT 月份 FROM 订单表)as a      
set @sql='SELECT 年度,'+@sql+' FROM 订单表 GROUP BY 年度 ORDER BY 年度'
exec(@sql)

--【4】使用stuff()——删除指定长度的字符,并在指定的起点处插入另一组字符。
declare @sql varchar(8000)
set @sql=''  --初始化变量@sql
select @sql=@sql+',"'+CAST(月份 AS VARCHAR(2))+'"' from 订单表 group by 月份    --变量多值赋值
set @sql=stuff(@sql,1,1,'')    --去掉首个','
set @sql='select * from 订单表 pivot (SUM(数量) for 月份 in ('+@sql+'))a'
exec(@sql)
--或使用isnull()
declare @sql varchar(8000)
select @sql=isnull(@sql+',"','"')+CAST(月份 AS VARCHAR(2))+'"' from 订单表 group by 月份
set @sql='select * from 订单表 pivot (SUM(数量) for 月份 in ('+@sql+'))a'
exec(@sql)


 

PIVOT的一般语法是:PIVOT(聚合函数(列) FOR列 in (…) )AS P

PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现

注意:PIVOT、UNPIVOT是SQL Server 2005 的语法,使用需修改数据库兼容级别

 

【待续……】

### SQL Server 动态列行转列的实现 在 SQL Server 中,当需要实现动态列的行转列操作时,可以采用 `PIVOT` 和动态 SQL 结合的方式。这种方法特别适合于列名不固定的情况。 #### 方法概述 为了实现动态列的行转列功能,通常会先通过查询获取所有的列名并将其拼接成字符串形式,随后利用该字符串构建动态 SQL 查询语句执行。以下是具体实现方式: #### 示例代码 假设有一个名为 `Scores` 的表结构如下: | Name | Subject | Score | |------|---------|-------| | 张三 | 语文 | 100 | | 张三 | 英语 | 80 | | 李四 | 数学 | 90 | 目标是将科目作为列展示出来,而分数则对应到相应的学生下。 ```sql -- 创建临时表用于演示 CREATE TABLE #Scores ( Name NVARCHAR(50), Subject NVARCHAR(50), Score INT ); INSERT INTO #Scores (Name, Subject, Score) VALUES ('张三', '语文', 100), ('张三', '英语', 80), ('李四', '数学', 90); ``` 接下来编写动态 SQL 脚本完成行转列的功能。 ```sql DECLARE @DynamicColumns NVARCHAR(MAX); DECLARE @SqlQuery NVARCHAR(MAX); -- 获取所有唯一的科目名称,并按逗号分隔连接起来 SELECT @DynamicColumns = STRING_AGG(QUOTENAME(Subject), ',') FROM (SELECT DISTINCT Subject FROM #Scores) AS Subjects; -- 构建完整的 PIVOT 查询语句 SET @SqlQuery = ' SELECT * FROM ( SELECT Name, Subject, Score FROM #Scores ) AS SourceTable PIVOT ( MAX(Score) FOR Subject IN (' + @DynamicColumns + ') ) AS PivotTable;'; -- 执行动态 SQL EXEC sp_executesql @SqlQuery; ``` 此脚本首先定义了一个变量存储动态生成的列列表 [@DynamicColumns],接着创建另一个变量保存整个 SQL 命令 [@SqlQuery] 并最终调用 EXEC 函数运行它 [^4]。 #### 解决方案说明 上述方法能够灵活应对未知数量或者不确定性的字段集合情况下的数据转换需求。通过使用 T-SQL 提供的强大字符串处理能力以及元编程技巧——即动态 SQL 技术,成功实现了从原始记录集向所需报表样式的转变过程 [^5]。 #### 注意事项 需要注意的是,在实际应用过程中可能会面临性能瓶颈等问题,尤其是在面对大规模数据集时更是如此。因此建议合理优化索引设置、控制参与运算的数据量范围等方式提升效率 [^6]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值