SQL SERVER【2】——行转列结果加上其他列

本文介绍如何使用SQL将表中的行数据转换为列数据,并进行数据聚合操作,包括固定列名的方法、动态生成列名及使用stuff()函数等技巧。

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

接上篇……

--二.行转列结果加上其他列
--【1】行转列结果+其他列

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月份",
SUM(数量)总数量,
CAST(avg(数量*1.0)as decimal(12,2)) 平均数量
FROM 订单表
GROUP BY 年度
ORDER BY 年度

--【2】静态SQL
select m.*,n.总数量,n.平均数量
from
(select * from 订单表 pivot (SUM(数量) for 月份 in("1","2","3","4","5","6","7","8","9","10","11","12"))a)m,
(select 年度,SUM(数量)总数量,CAST(avg(数量)as decimal(12,2)) 平均数量 from 订单表 group by 年度)n
where m.年度=n.年度
--【3】动态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 订单表)a
set @sql=@sql+',SUM(数量)总数量,CAST(avg(数量)as decimal(12,2)) 平均数量 from 订单表 group by 年度'
exec(@sql)


--【4】 动态SQL-使用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 m.*,n.总数量,n.平均数量 FROM 
(select * from 订单表 pivot (SUM(数量) for 月份 in ('+@sql+')) a) m ,
(select 年度,SUM(数量) 总数量,CAST(avg(数量) as decimal(12,2)) 平均数量 from 订单表 group by 年度) n 
where m.年度=n.年度'
exec(@sql)
--或使用isnull()
declare @sql varchar(8000)
select @sql=isnull(@sql+',"','"')+CAST(月份 AS VARCHAR(2))+'"' from 订单表 group by 月份
set @sql='SELECT m.*,n.总数量,n.平均数量 FROM
(select * from 订单表 pivot (SUM(数量) for 月份 in ('+@sql+'))a) m ,
(select 年度,SUM(数量) 总数量,CAST(avg(数量) as decimal(12,2)) 平均数量 from 订单表 group by 年度) n 
where m.年度=n.年度'
exec(@sql)



### SQL 中实现转行(透视)操作以汇总成绩 在SQL中,`PIVOT` 和 `UNPIVOT` 是用于转换数据方向的关键字。对于汇总成绩的需求,可以利用这些关键字来完成从到行的数据转换。 #### 使用 PIVOT 进行转行 假设有一个名为 `Scores` 的表,其中包含学生姓名 (`StudentName`)、科目名称 (`Subject`) 及其对应的分数 (`Score`) 字段: | StudentName | Subject | Score | |-------------|-------------|-------| | Alice | Math | 90 | | Bob | English | 85 | | Charlie | Science | 78 | 要将上述表格中的每一门课程的成绩按照行的形式展示出来,则可以通过如下查询语句实现: ```sql SELECT * FROM ( SELECT StudentName, Subject, CAST(Score AS VARCHAR(10)) AS Score FROM Scores ) p UNPIVOT ( Value FOR ColumnName IN ([Math], [English], [Science]) ) u; ``` 此命令会把原始表里的各科目的成绩按照指定的方式展开成多条记录[^2]。 然而,在实际应用过程中更常见的是先做一次聚合运算再进行旋转处理。比如计算每位同学每门课的平均分并将其转化为横向排结果集。此时可采用下面的方法: ```sql WITH AvgScores AS( SELECT StudentName, AVG(CASE WHEN Subject='Math' THEN Score ELSE NULL END) 'Math', AVG(CASE WHEN Subject='English' THEN Score ELSE NULL END)'English', AVG(CASE WHEN Subject='Science' THEN Score ELSE NULL END)'Science' FROM Scores GROUP BY StudentName ) SELECT * FROM AvgScores UNPIVOT(Value FOR Col IN([Math],[English],[Science]))AS unpvt; ``` 这段代码首先创建了一个临时视图 `AvgScores` 来存储每个学生的各科学习情况;接着使用 `UNPIVOT` 将之前得到的宽格式数据变窄,最终实现了期望的效果——即由原来的多个字段表示不同学科变为单个字段加上相应的值表达各个学科的信息[^4]。 请注意,具体语法可能会因所使用的数据库管理系统而异。以上例子适用于 Microsoft SQL Server 平台下的 T-SQL 编程环境。如果是在其他类型的 DBMS 上运行类似的查询,请参照相应产品的官方文档调整关键词和参数设置。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值