SQLServer使用Pivot和UnPivot实现行列转换的问题小结

Ella ·

更新时间:2024-09-20

· 493 次阅读

对于行列转换的数据，通常也就是在做报表的时候用的比较多，之前也零零散散的看了一些，今天就来总结一下。

先创建一个用于演示的临时表：

create table #temp
(
    年份    nvarchar(10)    null,
    月份    nvarchar(10)    null,
    数量    int        null
)
insert into #temp(年份,月份,数量)
select '2015','1','5645' union
select '2015','2','1234' union
select '2015','3','7982' union
select '2016','1','6465' union 
select '2016','2','7942' union
select '2016','3','8453' union
select '2017','1','4653' union
select '2017','2','1358' union
select '2017','3','7842' 
select * from #temp

下面来实现一些需求：

需求一，按年份分组，不同的月份为一列。

-- 按年份分组，不同的月份为一列
select t.年份,
sum(case t.月份 when '1' then t.数量 end) '1月份',
sum(case t.月份 when '2' then t.数量 end) '2月份',
sum(case t.月份 when '3' then t.数量 end) '3月份'
from #temp t
group by t.年份

另外两种方法：

-- 使用左外连接查询
select t.年份,t1.数量 '1月份',t2.数量 '2月份',t3.数量 '3月份' from #temp t
left join (select 年份,数量 from #temp where 月份='1') t1 on t.年份=t1.年份
left join (select 年份,数量 from #temp where 月份='2') t2 on t.年份=t2.年份
left join (select 年份,数量 from #temp where 月份='3') t3 on t.年份=t3.年份
group by t.年份,t1.数量,t2.数量,t3.数量
-- 使用自连接查询
select t.年份,t1.数量 '1月份',t2.数量 '2月份',t3.数量 '3月份' 
from #temp t,
(select 年份,数量 from #temp where 月份='1') t1,
(select 年份,数量 from #temp where 月份='2') t2,
(select 年份,数量 from #temp where 月份='3') t3
where t.年份=t1.年份 and t.年份=t2.年份 and t.年份=t3.年份
group by t.年份,t1.数量,t2.数量,t3.数量

返回的结果都是一样的，可以看见这几种方法都是可以实现的（当然，可能还有更多的方法待发掘），不过比起第一种方法，后面这两种方法也太低效了吧，比如一年有12个月份的数据，有个七八年的，那得写多少个子查询、表连接的，而且第一种方法也不是我们想要的。那么就需要用到 Pivot 这种方法了。

Pivot 语法：

table_source    -- 表名称，即数据源
    PIVOT(
    聚合函数（value_column）    -- value_column 要转换为 列值 的列名
    FOR pivot_column        -- pivot_column 指定要转换的列
    IN(<column_list>)        -- column_list 自定义的目标列名
)

因为这里列名不允许指定为数字，真是无语。。。我重建了一个数据结构一模一样的表。

create table #temp
(
    Name    nvarchar(10)    null,
    Course    nvarchar(10)    null,
    Score    int        null
)
insert into #temp(Name,Course,Score)
select '小李','语文','88' union
select '小李','数学','79' union
select '小李','英语','85' union
select '小明','语文','79' union 
select '小明','数学','89' union
select '小明','英语','87' union
select '小红','语文','84' union
select '小红','数学','76' union
select '小红','英语','92' 
select * from #temp
go

select Name 姓名,
max(case Course when '语文' then Score end) 语文,
max(case Course when '数学' then Score end) 数学,
max(case Course when '英语' then Score end) 英语,
sum(Score) 课程总分,
cast(avg(Score) as decimal(18,2)) 课程平均分
from #temp
group by Name

使用 Pivot 进行行转列：

select a.Name 姓名,a.语文,a.数学,a.英语
from #temp 
pivot
(
    max(Score)    -- 指定作为转换的列的值 的列名
    for Course        -- 指定要转换的列的列名
    in(语文,数学,英语)    -- 自定义的目标列名，即要转换列的不同的值作为列
)

select a.Name 姓名,a.语文,a.数学,a.英语,b.SumScore 课程总分,b.AvgScore 课程平均分
from #temp 
pivot
(
    max(Score)    -- 指定作为转换的列的值 的列名
    for Course        -- 指定要转换的列的列名
    in(语文,数学,英语)    -- 自定义的目标列名，即要转换列的不同的值作为列
)a,
(
    select t.Name,sum(t.Score) SumScore,cast(avg(t.Score) as decimal(18,2)) AvgScore
    from #temp t
    group by t.Name
)b
where a.Name=b.Name

UnPivot 语法：

table_source    -- 表名称，即数据源
    UNPIVOT(
    value_column    -- value_column 要转换为 行值 的列名
    FOR pivot_column    -- pivot_column 指定要转换为指定的列
    IN(<column_list>)    -- column_list 目标列名
)

create table #temp
(
    Name    nvarchar(10)    null,
    Chinese    int    null,
    Math    int    null,
    English int null
)
insert into #temp(Name,Chinese,Math,English)
select '小李','88','79','85' union
select '小明','79','89','87' union
select '小红','84','76','92' 
select * from #temp
go

select t.Name 姓名,t.Course 课程,t.Score 分数 from
(select t.Name,Course='Chinese',Score=Chinese from #temp t
union all
select t.Name,Course='Math',Score=Math from #temp t
union all
select t.Name,Course='English',Score=English from #temp t) t
order by t.Name,t.Course

select t.Name 姓名,t.Course 课程,t.Score 分数 from
(select t.Name,'Chinese' Course,Chinese Score from #temp t
union all
select t.Name,'Math',Math from #temp t
union all
select t.Name,'English',English from #temp t) t
order by t.Name,t.Course

使用 UnPivot 进行列转行：

select t.Name 姓名,t.Course 课程,t.Score 分数 
from #temp 
unpivot 
(
    Score for Course
    in(Chinese,Math,English)
)

到此这篇关于SQL Server 使用 Pivot 和 UnPivot 实现行列转换的文章就介绍到这了,更多相关SQL Server行列转换内容请搜索软件开发网以前的文章或继续浏览下面的相关文章希望大家以后多多支持软件开发网！

小结 sqlserver

1024 个赞

需要登录后方可回复, 如果你还没有账号请注册新账号

Markdown 代码

Farrah 2020-07-20

699

PHP7中I/O模型内核剖析详解

Madeleine 2021-06-10

538

NetBeans连接SQLserver数据库教程

Endora 2023-07-20

887

SqlServer数据库脚本执行命令行指令方式

Hoshi 2023-07-20

1644

SQLServer两表数据同步的多种方法详解

Isoke 2023-07-20

721

Sql数据库中去掉字段的所有空格小结篇

Gamila 2023-07-20

1009

ODBC连接数据库以SQLserver为例图文详解

Winema 2023-07-20

1925

SQLSERVER偏移函数(LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE)

Echo 2023-07-20

464

SQLServer通过端口1433连接到主机localhost的TCP/IP连接失败常见问题的解决方案

Damara 2023-07-20

544

SQLServer三种开窗函数详细用法

Irisa 2023-07-20

179

sqlserver查找括号()中字符串内容的方法实现

Ebony 2023-07-20

411

SQLServer2008每天自动备份数据库的图文教程

Vivienne 2023-07-20

1598

NetBeans连接SQLServer2008配置教程

Olive 2023-07-20

788

mysql和sqlserver查询数据库表的数量方法详解

Laila 2023-07-20

1665

Redis的常用命令小结

Mora 2023-07-20

185

C#Nullable的使用小结

Pelagia 2023-07-20

862

Python一键生成所有依赖包清单方法小结

Glory 2023-07-20

1410

C语言实现打印九九乘法表的四种方式小结

Galatea 2023-07-20

689

C++花括号{}初始化小结

Penny 2023-07-20

460

常用的34个Linux Shell脚本(小结)

Karli 2023-07-21

1407

我要提问

致谢

帮助他人，成就自己。

人生最大成功就是伸出热情而温暖的双手，尽自己所能去帮助身边的每一个人，只要无私的奉献，就会收获到美好的生活。

1024问感谢每一位朋友的帮助和支持。

软件开发网提供编程的基础软件技术培训教程,软件开发编程实例讲解Go,Node,HTML,CSS,Javascript,Python,Java,Ruby,C,PHP,MySQL等软件开发编程语言以及数据开发的基础知识，也提供大量的软件开发在线实例、从入门到精通就在1024问。

育儿网微养生全球行美食街育儿菜谱大全海南旅游女性养狗百科星座