pivot 和unpivot用法一目了然

最新推荐文章于 2025-02-08 12:01:28 发布

weixin_30322405

最新推荐文章于 2025-02-08 12:01:28 发布

阅读量249

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/max21011985/archive/2012/09/11/2680947.html

if object_id('tb')is not null
drop table tb
go

create table tb(
    Name varchar(10),
    Course varchar(10),
    Score int
)
go

insert    into tb values('张三','语文',74)
insert    into tb values('张三','数学',83)
insert    into tb values('张三','物理',93)
insert    into tb values('李四','语文',74)
insert    into tb values('李四','数学',84)
insert    into tb values('李四','物理',94)
go

select * from tb
go

--Old Way
select
    Name,
    MAX( case Course when '语文' then Score else 0 end) as 语文,
    MAX( case Course when '数学' then Score else 0 end) as 数学,
    MAX( case Course when '物理' then Score else 0 end) as 物理
from tb
group by Name

go

--PIVOT
select * from tb
pivot(
    max(Score) for Course in([语文],[数学],[物理])
    ) pivotedTb
order by Name
go

--UNPIVOT
select * from
(
    select * from tb
    pivot(
        max(Score) for Course in([语文],[数学],[物理])
    ) pivotedTb

) sourceTb

unpivot(
    Score for Course in([语文],[数学],[物理])
) unPivotedTb

简单演示了一下SQL Server2005 中的一对很有用的操作:PIVOT ,UNPIVOT

代码如下：

--Old Wayselect
    Name,
    MAX( case Course when '语文' then Score else 0 end) as 语文,
    MAX( case Course when '数学' then Score else 0 end) as 数学,
    MAX( case Course when '物理' then Score else 0 end) as 物理
from tb
group by Name

go

--PIVOT
select * from tb
pivot(
    max(Score) for Course in([语文],[数学],[物理])
    ) pivotedTb
order by Name
go

--UNPIVOT
select * from
(
    select * from tb
    pivot(
        max(Score) for Course in([语文],[数学],[物理])
    ) pivotedTb

) sourceTb

unpivot(
    Score for Course in([语文],[数学],[物理])
) unPivotedTb
go