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
运行结果:
name totalcount
LeeWhoee 3
DePaul 2
LeeWhoeeUniversity 2