对于行列转换的数据,通常也就是在做报表的时候用的比较多,之前也零零散散的看了一些,今天就来总结一下。
    先创建一个用于演示的临时表:
    使用 Pivot 和 UnPivot 实现行列转换 - 图1
    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
    使用 Pivot 和 UnPivot 实现行列转换 - 图2
    使用 Pivot 和 UnPivot 实现行列转换 - 图3
    下面来实现一些需求:
    需求一,按年份分组,不同的月份为一列。
    使用 Pivot 和 UnPivot 实现行列转换 - 图4
    — 按年份分组,不同的月份为一列
    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.年份
    使用 Pivot 和 UnPivot 实现行列转换 - 图5
    使用 Pivot 和 UnPivot 实现行列转换 - 图6
    另外两种方法:
    使用 Pivot 和 UnPivot 实现行列转换 - 图7
    — 使用左外连接查询
    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.数量
    使用 Pivot 和 UnPivot 实现行列转换 - 图8
    使用 Pivot 和 UnPivot 实现行列转换 - 图9
    返回的结果都是一样的,可以看见这几种方法都是可以实现的(当然,可能还有更多的方法待发掘),不过比起第一种方法,后面这两种方法也太低效了吧,比如一年有12个月份的数据,有个七八年的,那得写多少个子查询、表连接的,而且第一种方法也不是我们想要的。那么就需要用到 Pivot 这种方法了。

    Pivot 语法:
    使用 Pivot 和 UnPivot 实现行列转换 - 图10
    table_source — 表名称,即数据源

    1. PIVOT(
    2. 聚合函数(value_column -- value_column 要转换为 列值 的列名
    3. FOR pivot_column -- pivot_column 指定要转换的列
    4. IN(<column_list>) -- column_list 自定义的目标列名<br />)<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164491878-c23655dd-2744-4535-8d3c-a8a11bd64c7f.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />因为这里列名不允许指定为数字,真是无语。。。我重建了一个数据结构一模一样的表。<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164491875-bf0b854b-d550-4b34-9b34-4cb60c84e87b.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />create table #temp<br />(<br /> Name nvarchar(**10**) null,<br /> Course nvarchar(**10**) null,<br /> Score int null<br />)

    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
    使用 Pivot 和 UnPivot 实现行列转换 - 图11
    使用 Pivot 和 UnPivot 实现行列转换 - 图12
    使用 Pivot 和 UnPivot 实现行列转换 - 图13
    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 和 UnPivot 实现行列转换 - 图14
    使用 Pivot 和 UnPivot 实现行列转换 - 图15
    使用 Pivot 进行 行转列:
    使用 Pivot 和 UnPivot 实现行列转换 - 图16
    select a.Name 姓名,a.语文,a.数学,a.英语
    from #temp
    pivot
    (
    max(Score) — 指定作为转换的列的值 的列名
    for Course — 指定要转换的列的列名
    in(语文,数学,英语) — 自定义的目标列名,即要转换列的不同的值作为列
    )a
    使用 Pivot 和 UnPivot 实现行列转换 - 图17
    使用 Pivot 和 UnPivot 实现行列转换 - 图18
    使用 Pivot 和 UnPivot 实现行列转换 - 图19
    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
    使用 Pivot 和 UnPivot 实现行列转换 - 图20
    使用 Pivot 和 UnPivot 实现行列转换 - 图21

    *UnPivot 语法:

    使用 Pivot 和 UnPivot 实现行列转换 - 图22
    table_source — 表名称,即数据源

    UNPIVOT(
    
    value_column    -- value_column 要转换为 行值 的列名
    
    FOR pivot_column    -- pivot_column 指定要转换为指定的列
    
    IN(<column_list>)    -- column_list 目标列名<br />)<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164491942-e7225979-17ed-4ce8-8bd3-20895c19dedc.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164491938-0f8cd6fd-4d86-47eb-ac98-9e912889d936.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />create table #temp<br />(<br />    Name    nvarchar(**10**)    null,<br />    Chinese    int    null,<br />    Math    int    null,<br />    English int null<br />)
    

    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
    使用 Pivot 和 UnPivot 实现行列转换 - 图23
    使用 Pivot 和 UnPivot 实现行列转换 - 图24
    使用 Pivot 和 UnPivot 实现行列转换 - 图25
    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
    使用 Pivot 和 UnPivot 实现行列转换 - 图26
    使用 Pivot 和 UnPivot 实现行列转换 - 图27
    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
    使用 Pivot 和 UnPivot 实现行列转换 - 图28
    使用 Pivot 和 UnPivot 实现行列转换 - 图29
    *使用 UnPivot 进行 列转行:

    使用 Pivot 和 UnPivot 实现行列转换 - 图30
    select t.Name 姓名,t.Course 课程,t.Score 分数
    from #temp
    unpivot
    (
    Score for Course
    in(Chinese,Math,English)
    )t
    使用 Pivot 和 UnPivot 实现行列转换 - 图31
    使用 Pivot 和 UnPivot 实现行列转换 - 图32