SQL SERVER中PIVOT和UNPIVOT

PIVOT和UNPIVOT

PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。

基本准备：

create table TestPivot
(
    name varchar(50)
)
insert into testpivot(name)
select 'DePaul'
union all
select 'DePaul'
union all
select 'LeeWhoeeUniversity'
union all
select 'LeeWhoeeUniversity'
union all
select 'LeeWhoee'
union all
select 'LeeWhoee'
union all
select 'LeeWhoee'

用下面的查询当做一个表来操作

select name,count(*) as totalcount from testpivot
group by name

运行结果：

name totalcount
LeeWhoee 3
DePaul 2

LeeWhoeeUniversity 2

上面是我们将要操作的表数据

PIVOT

select 'totalcount' as name,[LeeWhoee],[DePaul],[LeeWhoeeUniversity]
    from
    (
    select name,count(*) as totalcount from testpivot
    group by name
    ) a
    pivot
    (
        max(totalcount) for name in ([LeeWhoee],[LeeWhoeeUniversity],[DePaul])
    ) b

运行结果：


name LeeWhoee DePaul LeeWhoeeUniversity
totalcount 3 2 2

UNPIVOT

下面使用UNPIVOT将此结果集反转成初始结果集

select _name as name,_totalcount as totalcount
from 
(
    select 'totalcount' as name,[LeeWhoee],[DePaul],[LeeWhoeeUniversity]
    from
    (
    select name,count(*) as totalcount from testpivot
    group by name
    ) a
    pivot
    (
        max(totalcount) for name in ([LeeWhoee],[LeeWhoeeUniversity],[DePaul])
    ) b
) d
unpivot
(
     _totalcount for _name  in([LeeWhoee],[DePaul],[LeeWhoeeUniversity])
) c