[Oracle] 行列转换(Tip)

数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。

SQL> SELECT company_name, city_id, sub_company_num
  2    FROM company_info;

COMPANY_NAME            CITY_ID SUB_COMPANY_NUM
-------------------- ---------- ---------------
HP                            3               2
HP                            1               3
HP                            2               1
IBM                           4               1
IBM                           1               4
Dell                          1               2
Dell                          3               2
Levono                        1               3
Levono                        4               1

9 rows selected.
SQL>
SQL> SELECT city_id, city_name
  2    FROM city_info;

   CITY_ID CITY_NAME
---------- -------------------------------
         1 Beijing
         2 Shanghai
         3 Hongkong
         4 Guangzhou

SQL> SELECT   company_name, MAX (DECODE (a.city_id,
  2                                      1, sub_company_num,
  3                                      0
  4                                     )) beijing,
  5           MAX (DECODE (a.city_id, 2, sub_company_num, 0)) shanghai,
  6           MAX (DECODE (a.city_id, 3, sub_company_num, 0)) hongkong,
  7           MAX (DECODE (a.city_id, 4, sub_company_num, 0)) guangzhou,
  8           (  MAX (DECODE (a.city_id, 1, sub_company_num, 0))
  9            + MAX (DECODE (a.city_id, 2, sub_company_num, 0))
 10            + MAX (DECODE (a.city_id, 3, sub_company_num, 0))
 11            + MAX (DECODE (a.city_id, 4, sub_company_num, 0))
 12           ) total
 13      FROM (SELECT company_name, c.city_id, sub_company_num
 14              FROM company_info c, city_info t
 15             WHERE c.city_id = t.city_id) a
 16  GROUP BY company_name
 17  /

COMPANY_NAME    BEIJING   SHANGHAI   HONGKONG  GUANGZHOU      TOTAL
------------ ---------- ---------- ---------- ---------- ----------
Dell                  2          0          2          0          4
HP                    3          1          2          0          6
IBM                   4          0          0          1          5
Levono                3          0          0          1          4


SQL>

这种方法在city_info表记录数比较少的情况下是比较方便的，但是如果city_info表记录数量很多，恐怕写SQL语句就要好长的一串。 可以考虑使用pipelined 函数来做，效果很好．

参考信息

http://www.oreillynet.com/pub/a/network/2003/01/22/feuerstein.html?page=1
http://www.akadia.com/services/ora_pipe_functions.html
http://www.psoug.org/reference/functions.html

本文作者

原文出处