PIVOT | UNPIVOT_1

Pivot应用

/*
   《Microsoft SQL Server 2008 T-SQL Fundamentals (PRO-Developer)》
   《Microsoft SQL Server 2012 T-SQL Fundamentals》
*/

if object_id('dbo.orders','U') is not null drop table dbo.orders;
GO
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)
);
GO
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20070802', 3, 'A', 10),
  (10001, '20071224', 2, 'A', 12),
  (10005, '20071224', 1, 'B', 20),
  (40001, '20080109', 2, 'A', 40),
  (10006, '20080118', 1, 'C', 14),
  (20001, '20080212', 2, 'B', 12),
  (40005, '20090212', 3, 'A', 10),
  (20002, '20090216', 1, 'C', 20),
  (30003, '20090418', 2, 'B', 15),
  (30004, '20070418', 3, 'C', 22),
  (30007, '20090907', 3, 'D', 30);
  
select * from orders

select empid,custid,sum(qty) as SumQty
from dbo.Orders
group by empid,custid
-----------------------------------------------------------------
--1
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
-----------------------------------------------------------------
--2
SELECT empid    --,orderid,orderdate,
  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 orderid, orderdate, empid;
-----------------------------------------------------------------
--3  使用PIVOT操作符转置
--PIVOT语法（此处还是将语法摆在此处，以形成一个理论形式）：
      select ...
      from (SourceTable or TableExpression)
           pivot (<agg_func>(<aggregation_element>)) for <spreading_element> in (<list_of_targe_columns>) as <result_table_alias>

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
-----------------------------------------------------------------
--4
SELECT empid, A, B, C, D
FROM dbo.Orders
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
-----------------------------------------------------------------
--5
SELECT custid, [1], [2], [3]
FROM (SELECT empid, custid, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR empid IN([1], [2], [3])) AS P;
  -----------------------------------------------------------------

  -----------------------------------------------------------------

 I strongly recommend that you never operate on the base table directly, even when the table contains only columns used as pivoting elements.
You never know whether new columns will be added to the table in the future, rendering your queries incorrect.
I recommend considering the use of a table expression as the input table to the PIVOT operator as if it were part of the requirement of the operator's syntax.

 

--1　　　　　　　　　　　　　　　　　　　　　　　　　-3　　　　　　　　　　　　　　--5
                        

 --2　　　　　　　　　　　　　　　　　　　　　　　　　--4
                 
_____________上面是PIVOT的训练------------------------

--------------------下面是UNPIVOT的训练，与PIVOT放在一起便于对比理解--------------------

----------UNPIVOT的应用 (Jun 3，增补)
  --就将PIVOT的结果集，当做UNPIVOT训练的源表，由此可产生出一个新表来以作为我们的“源”训练
  --我们要做的其实就是将 PIVOT的结果集旋转过来

  if object_id('EmpCustOrders','U') is not null drop EmpCustOrders;
  select empid,A,B,C,D
  into EmpCustOrders
  from (select empid,custid,qty
             from Orders) as F
       PIVOT (sum(qty) for custid in(A,B,C,D)) as p;

select * from EmpCustOrders

----------EmpCustOrders，现在就是我们操作的对象了
-----------------------------------------------------------------
--第1步
--1  (MSSQL2008, we can use a table value constructor in the form of values clause)
select * 
from EmpCustOrders
   cross join (values('A'),('B'),('C'),('D')) AS Custs(custid);
----------
--1.1  (before MSSQL2008, we need substitute the values clauses withe a series of select statements)
select * 
from EmpCustOrders
    cross join (select 'A' as custid
                      union all select 'B'
                      union all select 'C'
                      union all select 'D') as CustsABC;
-----------------------------------------------------------------
--第2步
--2
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 EmpCustOrders
   cross join (values('A'),('B'),('C'),('D')) AS Custs(custid)
--第3步（在第2步的基础上，去除那些 null 的数据），此步骤已经实现了旋转后的结果
--3
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 EmpCustOrders
               cross join (values('A'),('B'),('C'),('D')) AS Custs(custid)
       ) as p
where qty is not null;

--既然学习UNPIVOT，就用此法实现
--UNPIVOT语法
-PIVOT语法（此处还是将语法摆在此处，以形成一个理论形式）：
--3.1
    select ...
    from (SourceTable or TableExpression)
        unpivot <target_col_to_hold_source_col_values>            --->>>unpivot里面的参数不要进行聚合计算
        for <target_col_to_hold_source_col_names> in (<list_of_source_columns>) as <result_table_alias>    --

select empid,custid,qty
from EmpCustOrders
   unpivot(qty for custid in(A,B,C,D)) as p;           --

-- 我们将 PIVOT的结果集（ 参考PIVOT的运行结果 --5），生成了一个新表，
--select * from EmpCustOrders

 

 
    --1                                                                     --2                                   --3                                    --3.1（UNPIVOT方式）