pivot and unpivot

转载于http://dev.21tx.com/2008/12/16/13997.html
Pivot

　　如您所知，关系表是表格化的，即，它们以列-值对的形式出现。假设一个表名为 CUSTOMERS.

SQL> desc customers
Name　　　　　　　　　　　　　　　　　　　Null?　　Type
----------------------------------------- -------- ---------------------------
CUST_ID　　　　　　　　　　　　　　　　　　　　　　NUMBER(10)
CUST_NAME　　　　　　　　　　　　　　　　　　　　　VARCHAR2(20)
STATE_CODE　　　　　　　　　　　　　　　　　　　　 VARCHAR2(2)
TIMES_PURCHASED　　　　　　　　　　　　　　　　　　NUMBER(3)

　　选定该表：

select cust_id, state_code, times_purchased
from customers
order by cust_id;

　　输出结果如下：

CUST_ID STATE_CODE TIMES_PURCHASED
------- ---------- ---------------
　　　1 CT　　　　　　　　　　　 1
　　　2 NY　　　　　　　　　　　10
　　　3 NJ　　　　　　　　　　　 2
　　　4 NY　　　　　　　　　　　 4
... and so on ...

　　注意数据是如何以行值的形式显示的：针对每个客户，该记录显示了客户所在的州以及该客户在商店购物的次数。当该客户从商店购买更多物品时，列 times_purchased 会进行更新。

　　现在，假设您希望统计一个报表，以了解各个州的购买频率，即，各个州有多少客户只购物一次、两次、三次等等。如果使用常规 SQL，您可以执行以下语句：

select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;

　　输出如下：

ST TIMES_PURCHASED　　　　CNT
-- --------------- ----------
CT　　　　　　　 0　　　　 90
CT　　　　　　　 1　　　　165
CT　　　　　　　 2　　　　179
CT　　　　　　　 3　　　　173
CT　　　　　　　 4　　　　173
CT　　　　　　　 5　　　　152
... and so on ...

　　这就是您所要的信息，但是看起来不太方便。使用交叉表报表可能可以更好地显示这些数据，这样，您可以垂直排列数据，水平排列各个州，就像电子表格一样：

Times_purchased
　　　　　　 CT　　　　　 NY　　　　 NJ　　　... and so on ...
1　　　　　　 0　　　　　　1　　　　　0　　　...
2　　　　　　23　　　　　119　　　　 37　　　...
3　　　　　　17　　　　　 45　　　　　1　　　...
... and so on ...

　　在 Oracle 数据库 11g 推出之前，您需要针对每个值通过 decode 函数进行以上操作，并将每个不同的值编写为一个单独的列。但是，该方法一点也不直观。

　　庆幸的是，您现在可以使用一种很棒的新特性 PIVOT 通过一种新的操作符以交叉表格式显示任何查询，该操作符相应地称为 pivot。下面是查询的编写方式：

select * from (
　 select times_purchased, state_code
　 from customers t
)
pivot
(
　 count(state_code)
　 for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
/

　　输出如下：

. TIMES_PURCHASED　　　 'NY'　　　 'CT'　　　 'NJ'　　　 'FL'　　　 'MO'
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
　　　　　　　　　　　　　　　　　　　0　　　16601　　　　 90　　　　　0　　　　　0　　　　　0
　　　　　　　　　　　　　　　　　　　1　　　33048　　　　165　　　　　0　　　　　0　　　　　0
　　　　　　　　　　　　　　　　　　　2　　　33151　　　　179　　　　　0　　　　　0　　　　　0
　　　　　　　　　　　　　　　　　　　3　　　32978　　　　173　　　　　0　　　　　0　　　　　0
　　　　　　　　　　　　　　　　　　　4　　　33109　　　　173　　　　　0　　　　　1　　　　　0
... and so on ...

　　这表明了 pivot 操作符的威力。state_codes 作为标题行而不是列显示。下面是传统的表格化格式的图示：

　　图 1 传统的表格化显示

　 　在交叉表报表中，您希望将 Times Purchased 列的位置掉换到标题行，如图 2 所示。该列变为行，就好像该列逆时针旋转 90 度而变为标题行一样。该象征性的旋转需要有一个支点 （pivot point），在本例中，该支点为 count（state_code） 表达式。

　　图 2 执行了 Pivot 操作的显示

　　该表达式需要采用以下查询语法：

...
pivot
(
　 count(state_code)
　 for state_code in ('NY','CT','NJ','FL','MO')
)
...

　　第二行“for state_code ……”限制查询对象仅为这些值。该行是必需的，因此不幸的是，您需要预先知道可能的值。该限制在 XML 格式的查询将有所放宽，如本文后面部分所述。

　　注意输出中的标题行：

. TIMES_PURCHASED　　　 'NY'　　　 'CT'　　　 'NJ'　　　 'FL'　　　 'MO'
　--------------- ---------- ---------- ---------- ---------- ----------

　　列标题是来自表本身的数据：州代码。缩写可能已经相当清楚无需更多解释，但是假设您希望显示州名而非缩写（“Connecticut”而非“CT”），那又该如何呢？如果是这样，您需要在查询的 FOR 子句中进行一些调整，如下所示：

select * from (
　 select times_purchased as "Puchase Frequency", state_code
　 from customers t
)
pivot
(
　 count(state_code)
　 for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
)
order by 1
/
Puchase Frequency　 New York Connecticut New Jersey　　Florida　 Missouri
----------------- ---------- ----------- ---------- ---------- ----------
　　　　　　　　0　　　16601　　　　 90　　　　　 0　　　　　0　　　　　0
　　　　　　　　1　　　33048　　　　165　　　　　 0　　　　　0　　　　　0
　　　　　　　　2　　　33151　　　　179　　　　　 0　　　　　0　　　　　0
　　　　　　　　3　　　32978　　　　173　　　　　 0　　　　　0　　　　　0
　　　　　　　　4　　　33109　　　　173　　　　　 0　　　　　1　　　　　0
... and so on ...

　　FOR 子句可以提供其中的值（这些值将成为列标题）的别名。

　　Unpivot

　　就像有物质就有反物质一样，有 pivot 就应该有“unpivot”，对吧？

　　好了，不开玩笑，但 pivot 的反向操作确实需要。假设您有一个显示交叉表报表的电子表格，如下所示：

Purchase Frequency	New York	Connecticut	New Jersey	Florida	Missouri
0	12	11	1	0	0
1	900	14	22	98	78
2	866	78	13	3	9
...	.

　　现在，您希望将这些数据加载到一个名为 CUSTOMERS 的关系表中：

SQL> desc customers
Name　　　　　　　　　　　　　　　　　　　Null?　　Type
----------------------------------------- -------- ---------------------------
CUST_ID　　　　　　　　　　　　　　　　　　　　　　NUMBER(10)
CUST_NAME　　　　　　　　　　　　　　　　　　　　　VARCHAR2(20)
STATE_CODE　　　　　　　　　　　　　　　　　　　　 VARCHAR2(2)
TIMES_PURCHASED　　　　　　　　　　　　　　　　　　NUMBER(3)

　 　必须将电子表格数据去规范化为关系格式，然后再进行存储。当然，您可以使用 DECODE 编写一个复杂的 SQL*：Loader 或 SQL 脚本，以将数据加载到 CUSTOMERS 表中。或者，您可以使用 pivot 的反向操作 UNPIVOT，将列打乱变为行，这在 Oracle 数据库 11g 中可以实现。

　　通过一个示例对此进行演示可能更简单。让我们首先使用 pivot 操作创建一个交叉表：

　1　create table cust_matrix
　2　as
　3　select * from (
　4　　 select times_purchased as "Puchase Frequency", state_code
　5　　 from customers t
　6　)
　7　pivot
　8　(
　9　　 count(state_code)
10　　 for state_code in ('NY' as "New York",'CT' "Conn",'NJ' "New Jersey",'FL' "Florida",
'MO' as "Missouri")
11　)
12* order by 1

　　您可以查看数据在表中的存储方式：

SQL> select * from cust_matrix
　2　/
Puchase Frequency　 New York　　　 Conn New Jersey　　Florida　 Missouri
----------------- ---------- ---------- ---------- ---------- ----------
　　　　　　　　1　　　33048　　　　165　　　　　0　　　　　0　　　　　0
　　　　　　　　2　　　33151　　　　179　　　　　0　　　　　0　　　　　0
　　　　　　　　3　　　32978　　　　173　　　　　0　　　　　0　　　　　0
　　　　　　　　4　　　33109　　　　173　　　　　0　　　　　1　　　　　0
... and so on ...

　　这是数据在电子表格中的存储方式：每个州是表中的一个列（“New York”、“Conn”等等）。

SQL> desc cust_matrix
Name　　　　　　　　　　　　　　　　　　　　　　　 Null?　　　　　　　　 Type
----------------------------------------- -------- ---------------------------
Puchase Frequency　　　　　　　　　　　　　　　　　　　　　　　　　NUMBER(3)
New York　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　NUMBER
Conn　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　NUMBER
New Jersey　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　NUMBER
Florida　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 NUMBER
Missouri　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　NUMBER

　　您需要将该表打乱，使行仅显示州代码和该州的购物人数。通过 unpivot 操作可以达到此目的，如下所示：

select *
　from cust_matrix
unpivot
(
　state_counts
　　for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code
/

　　输出如下：

Puchase Frequency STATE_CODE STATE_COUNTS
----------------- ---------- ------------
　　　　　　　　1 Conn　　　　　　　　165
　　　　　　　　1 Florida　　　　　　　 0
　　　　　　　　1 Missouri　　　　　　　0
　　　　　　　　1 New Jersey　　　　　　0
　　　　　　　　1 New York　　　　　33048
　　　　　　　　2 Conn　　　　　　　　179
　　　　　　　　2 Florida　　　　　　　 0
　　　　　　　　2 Missouri　　　　　　　0
... and so on ...

　　注意每个列名如何变为 STATE_CODE 列中的一个值。Oracle 如何知道 state_code 是一个列名？它是通过查询中的子句知道的，如下所示：

for state_code in ("New York","Conn","New Jersey","Florida","Missouri")

　　这里，您指定“New York”、“Conn”等值是您要对其执行 unpivot 操作的 state_code 新列的值。我们来看看部分原始数据：

Puchase Frequency　 New York　　　 Conn New Jersey　　Florida　 Missouri
----------------- ---------- ---------- ---------- ---------- ----------
　　　　　　　　1　　　33048　　　　165　　　　　0　　　　　0　　　　　0

　　当列“纽约”突然变为一个行中的值时，您会怎样显示值 33048 呢？该值应该显示在哪一列下呢？上述查询中 unpivot 操作符内的 for 子句上面的子句对此进行了解答。您指定了 state_counts，它就是在生成的输出中创建的新列的名称。

　 　Unpivot 可以是 pivot 的反向操作，但不要以为前者可以对后者所进行的任何操作进行反向操作。例如，在上述示例中，您对 CUSTOMERS 表使用 pivot 操作创建了一个新表 CUST_MATRIX.然后，您对 CUST_MATRIX 表使用了 unpivot，但这并没有取回原始表 CUSTOMERS 的详细信息。相反，交叉表报表以便于您将数据加载到关系表中的不同方式显示。因此 unpivot 并不是为了取消 pivot 所进行的操作。在使用 pivot 创建一个表然后删除原始表之前，您应该慎重考虑。

　　 unpivot 的某些很有趣的用法超出了通常的强大数据操作功能范围（如上面的示例）。Amis Technologies 的 Oracle ACE 总监 Lucas Jellema 介绍了如何生成若干行特定数据用于测试。在此，我将对他的原始代码稍加修改，以显示英语字母表中的元音：

select value
from
(
　　(
　　　　select
　　　　　　'a' v1,
　　　　　　'e' v2,
　　　　　　'i' v3,
　　　　　　'o' v4,
　　　　　　'u' v5
　　　　from dual
　　)
　　unpivot
　　(
　　　　value
　　　　for value_type in
　　　　　　(v1,v2,v3,v4,v5)
　　)
)
/

　　输出如下：

V
-
a
e
i
o
u

　　该模型可以扩展为包含任何类型的行生成器。感谢 Lucas 为我们提供了这一巧妙招术。

　　XML 类型

　　在上述示例中，注意您指定有效的 state_codes 的方式：

for state_code in ('NY','CT','NJ','FL','MO')

　　该要求假设您知道 state_code 列中显示的值。如果您不知道都有哪些值，您怎么构建查询呢？

　　pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出，在此输出中，您可以指定一个特殊的子句 ANY 而非文字值。示例如下：

select * from (
　 select times_purchased as "Purchase Frequency", state_code
　 from customers t
)
pivot xml
(
　 count(state_code)
　 for state_code in (any)
)
order by 1
/

　　输出恢复为 CLOB 以确保 LONGSIZE 在查询运行之前设置为大值。

SQL> set long 99999

　 　较之原始的 pivot 操作，该查询有两处明显不同（用粗体显示）。首先，您指定了一个子句 pivot xml 而不只是 pivot.该子句生成 XML 格式的输出。其次，for 子句显示 for state_code in （any） 而非长列表的 state_code 值。该 XML 表示法允许您使用 ANY 关键字，您不必输入 state_code 值。输出如下：

Purchase Frequency STATE_CODE_XML
------------------ --------------------------------------------------
　　　　　　　　 1 CT　　　　　　　　　 lumn>165　　　　　　　　　 n>NY　　　　　　　　　 umn>33048　　　　　　　　　 mn>
　　　　　　　　 2 CT　　　　　　　　　 lumn>179　　　　　　　　　 n>NY　　　　　　　　　 umn>33151　　　　　　　　　 mn>
... and so on ...

　　如您所见，列 STATE_CODE_XML 是 XMLTYPE，其中根元素是 .每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。

　　除了 ANY 子句外，您还可以编写一个子查询。假设您有一个优先州列表并希望仅选择这些州的行。您将优先州放在一个名为 preferred_states 的新表中：

SQL> create table preferred_states
　2　(
　3　　 state_code varchar2(2)
　4　)
　5　/
Table created.
SQL> insert into preferred_states values ('FL')
　2> /
1 row created.
SQL> commit;
Commit complete.

　　现在 pivot 操作如下所示：

select * from (
　 select times_purchased as "Puchase Frequency", state_code
　 from customers t
)
pivot xml
(
　 count(state_code)
　 for state_code in (select state_code from preferred_states)
)
order by 1
/

　　for 子句中的子查询可以是您需要的任何内容。例如，如果希望选择所有记录而不限于任何优先州，您可以使用以下内容作为 for 子句：

for state_code in (select distinct state_code from customers)

　　子查询必须返回不同的值，否则查询将失败。这就是我们指定上述 DISTINCT 子句的原因。

　　结论

　 　Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表，而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样，您可以使用 unpivot 操作转换任何交叉表报表，以常规关系表的形式对其进行存储。Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出，您不必指定 pivot 操作需要搜索的值域。

实验（转自ITPUB）

2011-11-12 列转行
作者：Kim Berg Hansen
难度：高

我创建了如下的表并填入数据:
CREATE TABLE plch_employees
(
   employee_id    INTEGER
, last_name      VARCHAR2 (20)
, hire_date      DATE
, promote_date   DATE
, fire_date      DATE
)
/

INSERT INTO plch_employees
VALUES (
   100,
   'Jobs',
   DATE '1995-01-01',
   DATE '2005-01-01',
   DATE '2010-01-01'
)
/

INSERT INTO plch_employees
VALUES (
   200,
   'Ellison',
   DATE '1990-01-01',
   DATE '2000-01-01',
   NULL
)
/

INSERT INTO plch_employees
VALUES (
   300,
   'Gates',
   DATE '2001-01-01',
   NULL,
   DATE '2002-01-01'
)
/

COMMIT
/

我老板想要一张 HR 部门的录用、提升、解雇时间的时间表。换句话说，他想要一个清单，包含了事件日期、事件（录用、提升或解雇）和员工的姓。

哪些选项能够产生这样一个清单（如下所示）包含了上述的七个HR事件？

EVENT_DATE    EVENT      LAST_NAME                    
------------- ---------- ------------------------------
01-JAN-90     Hire       Ellison                      
01-JAN-95     Hire       Jobs                         
01-JAN-00     Promote    Ellison                      
01-JAN-01     Hire       Gates                        
01-JAN-02     Fire       Gates                        
01-JAN-05     Promote    Jobs                         
01-JAN-10     Fire       Jobs 


(A)
SELECT event_date
     , event
     , last_name
FROM plch_employees
UNPIVOT INCLUDE NULLS (
   event_date FOR event IN (
      hire_date AS 'Hire',
      promote_date AS 'Promote',
      fire_date AS 'Fire'
   )
)
ORDER BY event_date;

(B)
SELECT CASE d.dummy
          WHEN 1 THEN e.hire_date
          WHEN 2 THEN e.promote_date
          WHEN 3 THEN e.fire_date
       END event_date,
       CASE d.dummy
          WHEN 1 THEN 'Hire'
          WHEN 2 THEN 'Promote'
          WHEN 3 THEN 'Fire'
       END event,
       e.last_name
FROM plch_employees e
CROSS JOIN (
   SELECT LEVEL AS dummy
   FROM DUAL b 
   CONNECT BY LEVEL <= 3
) d
ORDER BY event_date;

(C)
SELECT event_date
     , event
     , last_name
FROM plch_employees
PIVOT (
   MAX(event_date)
   FOR event IN (
      hire_date AS 'Hire',
      promote_date AS 'Promote',
      fire_date AS 'Fire'
   )
)
ORDER BY event_date;

(D)
SELECT event_date
     , event
     , last_name
FROM plch_employees
UNPIVOT (
   event_date FOR event IN (
      hire_date AS 'Hire',
      promote_date AS 'Promote',
      fire_date AS 'Fire'
   )
)
ORDER BY event_date;

(E)
WITH events AS (
   SELECT 'Hire'    event FROM DUAL
   UNION ALL
   SELECT 'Promote' event FROM DUAL
   UNION ALL
   SELECT 'Fire'    event FROM DUAL
)
SELECT event_date
     , event
     , last_name
FROM (
   SELECT CASE events.event
             WHEN 'Hire'    THEN e.hire_date
             WHEN 'Promote' THEN e.promote_date
             WHEN 'Fire'    THEN e.fire_date
          END event_date,
          events.event,
          e.last_name
   FROM plch_employees e
   CROSS JOIN events
) evt
WHERE event_date IS NOT NULL
ORDER BY event_date;

(F)select event_date
     , event
     , last_name
FROM (
   SELECT CASE d.dummy
             WHEN 1 THEN e.hire_date
             WHEN 2 THEN e.promote_date
             WHEN 3 THEN e.fire_date
          END event_date,
          CASE d.dummy
             WHEN 1 THEN 'Hire'
             WHEN 2 THEN 'Promote'
             WHEN 3 THEN 'Fire'
          END event,
          e.last_name
   FROM plch_employees e
   CROSS JOIN (
      SELECT LEVEL AS dummy
      FROM DUAL
      CONNECT BY LEVEL <= 3
   ) d
) evt
WHERE event_date IS NOT NULL
ORDER BY event_date;

2011-11-12 答案DEF
A:UNPIVOT是对的，但是INCLUDE NULLS产生了9行输出，（3列×3行）
B:和三行集合做笛卡尔积是对的，但是依然包含了NULL，结果同上。
C:UNPIVOT(列转行)和PIVOT(行转列)的用法搞反了。
D:UNPIVOT缺省是EXCLUDE NULLS的，相当于修正了A的写法。
E:和三行集合做笛卡尔积是对的，并且过滤掉 NULL, 相当于DIY的UNPIVOT
F:类似E的实现方法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26844646/viewspace-749240/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26844646/viewspace-749240/