http://technet.microsoft.com/zh-cn/library/ms177410.aspx
unpivot :
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
select * from pvt
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
结果5行 6列变 25 行 3列 。
pivot :
create table T3 ( id int , name nvarchar(20), score int ) use DBtest insert T3 values (1,'语文',100), (1,'数学',100), (1,'英语',100), (2,'语文',200), (2,'数学',200), (3,'语文',10), (3,'数学',100), (4,'英语',100)
select id,sum(case name when '数学' then score end)'数学' , sum(case name when '语文' then score end) '语文' , sum(case name when '英语' then score end) '英语' from T3 group by id
select id, 语文,数学,英语 from ( select id,name,score from T3 ) as D pivot (sum(score) for name in (语文,数学,英语)) as P
这2 种查询 ，结果一样
我的理解是 pivot 和 unpivot是 case when 的封装 ，就一个语法糖