第7章 透视、逆透视及分组集

use tempdb;
go

--第7章 透视、逆透视及分组集
--7.1 透视转换
--透视转换(pivoting)是一种把数据从行的状态转换为列的状态的处理
if OBJECT_ID('dbo.orders', 'U') is not null 
drop table dbo.orders;
create table dbo.orders
(orderid int not null,
orderdate date not null,
empid int not null,
custid varchar(5) not null,
qty int not null,
constraint PK_Orders primary key(orderid)
);

insert into dbo.orders(orderid, orderdate, empid, custid, qty)
values
(30001, N'20070802', 3, 'A', 10),
(10001, N'20071224', 2, 'A', 12),
(10005, N'20071224', 1, 'B', 20),
(40001, N'20080109', 2, 'A', 40),
(10006, N'20080118', 1, 'C', 14),
(20001, N'20080212', 2, 'B', 12),
(40005, N'20090212', 3, 'A', 10),
(20002, N'20090216', 1, 'C', 20),
(30003, N'20090418', 2, 'B', 15),
(30004, N'20070418', 3, 'C', 22),
(30007, N'20090907', 3, 'D', 30);

select * from dbo.orders;

select empid, custid, SUM(qty) as sumqty
from dbo.orders
group by empid, custid

--7.1.1 使用标准SQL进行透视转换
--扩展阶段通过在SELECT子句中为每个目标指定CASE表达式来实现
select empid,
SUM(case when custid = 'A' then qty end) as A,
SUM(case when custid = 'B' then qty end) as B,
SUM(case when custid = 'C' then qty end) as C,
SUM(case when custid = 'D' then qty end) as D
from dbo.orders
group by empid

--7.1.2 使用T-SQL PIVOT运算符进行透视转换
--对于PIVOT运算符有个重要的地方须要注意：不须要为它显式地指定分组元素，
--也就不需要在查询中使用GROUP BY子句。PIVOT运算符隐式地把源表(或表表达式)
--中即没有指定为扩展元素，也没有指定为聚合元素的那些元素作为分组元素
select empid, A, B, C, D
from(
select empid, custid, qty
from dbo.orders) as D
PIVOT(sum(qty) for custid in(A, B, C, D)) as P

select custid, [1], [2], [3]
from (select empid, custid, qty from dbo.orders) as o
pivot(sum(qty) for empid in ([1],[2],[3])) as p


--7.2 逆透视转换
--逆透视转换(unqivoting)是一种把数据从列的状态旋转为行的状态的技术
if OBJECT_ID('dbo.EmpCustOrders', 'U') is not null
drop table	dbo.EmpCustOrders

select empid, A, B, C, D
into dbo.EmpCustOrders
from (select empid, custid, qty from dbo.orders) as tt
pivot(sum(qty) for custid in (A, B, C, D)) as p

select * from dbo.EmpCustOrders

select * from(
select empid, custid, 
case custid 
when 'A' then A
when 'B' then B
when 'C' then C
when 'D' then D
end as qty
from dbo.EmpCustOrders
cross join (values('A'), ('B'), ('C'), ('D')) as c(custid)) as d
where qty is not null

--7.2.2 使用TSQL的UNPIVOT运算符进行逆透视转换
select empid, custid, qty
from dbo.EmpCustOrders
unpivot(qty for custid in (A, B, C, D)) as up;