unpivot,pivot,case when ,行转列，列转行 sql server

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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 的封装，就一个语法糖