SQLServer透视和逆透视(行转列)

--透视
--======================================================--
use tempdb
--判断是否存在OBJECT_ID('dbo.orders','U')对象，第二个参数U代表是用户创建的
if OBJECT_ID('dbo.orders','U') is not null drop table dbo.orders
create table dbo.orders
(
    orderid int not null primary key,
    empid int not null,
    custid int not null,
    orderdate datetime,
    qty int
)

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

--分组查询
select empid,custid,sum(qty) sumQty from orders group by empid,custid

--1.对雇员进行分组
select empid,sum(qty) from orders group by empid

--可以计算某个顾客被某个雇员销售的数量
select sum(qty) from orders where empid=1 and custid=2

--2.增加顾客的列, 1号顾客
select empid,
(select sum(qty) from orders where empid=o.empid and custid=1) as 一号顾客,
(select sum(qty) from orders where empid=o.empid and custid=2) as 二号顾客,
(select sum(qty) from orders where empid=o.empid and custid=3) as 三号顾客,
(select sum(qty) from orders where empid=o.empid and custid=4) as 四号顾客
from orders o group by empid

--使用case...end,这里的sum(case when custid=1 then qty end)代表只有custid=1的qty做合计
select empid,sum(case when custid=1 then qty end) as 一号顾客,
sum(case when custid=2 then qty end) as 二号顾客,
sum(case when custid=3 then qty end) as 三号顾客,
sum(case when custid=4 then qty end) as 四号顾客
 from orders group by empid

--使用pivot
 use tempdb

select empid,[1] as 顾客1,[2] as 顾客2,[3] as 顾客3,[4] as 顾客4 
from ( --返回透视中用到的列
select empid,qty,custid from orders) t --分组是隐含的, 对表中除了聚合sum(qty)和条件for custid in ([1],[2],[3],[4])的列进行分组
pivot (
    sum(qty) --聚合函数
    for custid in ([1],[2],[3],[4]) --准备做列名(也就是要对custid列的哪些值作为列名)
    ) as p;

select * from orders
--错误的写法(原因没有对要用到透视中的列进行处理).因为orders里面有很多列,除了聚合和条件以下语句还会对empid,orderid,orderdate一起分组,所以结果会得到很多数据
select empid,[1],[2],[3],[4] 
from orders
pivot (
    sum(qty)
    for custid in ([1],[2],[3],[4]) 
    ) as p;

--练习
use TSQLFundamentals2008

select empid,count(1) as 总计,count(case when year(orderdate)='2006' then orderdate end) as '2006年',
 count(case when year(orderdate)='2007' then orderdate end) as '2007年',
  (select count(1) from Sales.Orders where year(orderdate)='2008' and empid=o.empid)as '2008年'
  from Sales.Orders o group by empid

select * from Sales.Orders

--使用pivot
select empid,[2006],[2007],[2008] from (select empid,year(orderdate) as orderyear,orderid from Sales.Orders) t pivot (
    count(orderid) for orderyear in ([2006],[2007],[2008])
) as p
order by empid
--======================================================--

 逆透视:

--逆透视
use tempdb

if object_id('dbo.EmpCustOrders','U') is not null
drop table dbo.EmpCustOrders

select empid,[1],[2],[3],[4] into dbo.EmpCustOrders
from ( 
select empid,qty,custid from orders) t 
pivot (
    sum(qty) --聚合函数
    for custid in ([1],[2],[3],[4]) 
    ) as p;

select * from dbo.EmpCustOrders

--逆透视,列转行
--交叉连接
select * from (
select empid,custid,(case 
        when custid=1 then [1]
        when custid=2 then [2]
        when custid=3 then [3]
        else [4]
        end) as qty from 
dbo.EmpCustOrders e cross join (
    select 1 as custid
    union all
    select 2
    union all
    select 3
    union all 
    select 4
) as c
) as t where qty is not null

--使用unpivot
select empid,custid,qty from dbo.EmpCustOrders unpivot(qty for custid in ([1],[2],[3],[4])) as u