select pivot

从Oracle 11.1开始,oracle在SELECT中引入了pivot和unpivot两个语法。
按照官方文档的说法,PIVOT是将ROWS转换成COLUMNS,UNPIVOT是将COLUMNS转换成ROWS。
本文针对pivot语法进行测试,测试环境为11.2.0.4,使用Oracle Demo用户HR的employees表。
函数的语法如下
SELECT …
FROM …
PIVOT [XML]
( pivot_clause
pivot_for_clause
pivot_in_clause
)
WHERE …

语法说明
Pivot_clause用于声明参与聚合的列;
Pivot_for_clause定义被分组或PIVOT的列;
Pivot_in_clause定义在pivot_for_clause中列的过滤条件,在pivot_in_clause中对每个值的聚合都将被变换为分别的列。

原始数据

SQL> select department_id,manager_id,job_id,salary from  employees where department_id <= 40;

DEPARTMENT_ID MANAGER_ID JOB_ID                   SALARY
------------- ---------- -------------------- ----------
           10        101 AD_ASST                    4400
           20        100 MK_MAN                    13000
           20        201 MK_REP                     6000
           30        100 PU_MAN                    11000
           30        114 PU_CLERK                   2600
           30        114 PU_CLERK                   2900
           30        114 PU_CLERK                   3100
           30        114 PU_CLERK                   2800
           30        114 PU_CLERK                   2500
           40        101 HR_REP                     6500
已选择10行。

现在查询各部门各JOB类型的总薪水。

SQL> select department_id, job_id, sum(salary) as sum_salary
  2    from employees
  3   where department_id < = 40
  4   group by department_id, job_id
  5   order by 1,2
  6  ;

DEPARTMENT_ID JOB_ID               SUM_SALARY
------------- -------------------- ----------
           10 AD_ASST                    4400
           20 MK_MAN                    13000
           20 MK_REP                     6000
           30 PU_CLERK                  13900
           30 PU_MAN                    11000
           40 HR_REP                     6500

现在如果我们想要展示每个JOB类型都作为一列显示的话会更加直观一些,在不适用pivot的前提下需要适用DECODE函数来实现。

SQL> select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   order by 1;

DEPARTMENT_ID    AD_ASST     MK_MAN     MK_REP   PU_CLERK     PU_MAN     HR_REP
------------- ---------- ---------- ---------- ---------- ---------- ----------
           10       4400
           20                 13000       6000
           30                                       13900      11000
           40                                                              6500

使用pivot来实现

SQL> select * from
  2    (select department_id,job_id,salary from employees where department_id <= 40)
  3  pivot(
  4       sum(salary)     --pivot_clause
  5       for job_id      --pivot_for_clause
  6       in (            --filter
  7      'AD_ASST' as AD_ASST,
  8      'MK_MAN' as MK_MAN,
  9      'MK_REP' as MK_REP,
 10      'PU_CLERK' as PU_CLERK,
 11      'PU_MAN' as PU_MAN,
 12      'HR_REP' as HR_REP
 13       )
 14  )
 15  order by 1;

DEPARTMENT_ID    AD_ASST     MK_MAN     MK_REP   PU_CLERK     PU_MAN     HR_REP
------------- ---------- ---------- ---------- ---------- ---------- ----------
           10       4400
           20                 13000       6000
           30                                       13900      11000
           40                                                              6500

执行计划
Plan hash value: 2508337833

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |        |       |     2 (100)|
|   1 |  SORT GROUP BY NOSORT PIVOT  |                   |      1 |    16 |     2   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     10 |   160 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     10 |       |     1   (0)|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID" with pivot_employees as (select department_id,job_id,salary from employees where department_id <= 40)
  2  select * from  pivot_employees
  3  pivot(
  4       sum(salary) as salary     --别名追加到列名后面
  5       for job_id
  6       in (
  7      'AD_ASST' as AD_ASST,
  8      'MK_MAN' as MK_MAN,
  9      'MK_REP' as MK_REP,
 10      'PU_CLERK' as PU_CLERK,
 11      'PU_MAN' as PU_MAN,
 12      'HR_REP' as HR_REP
 13       )
 14  )
 15  order by 1;

DEPARTMENT_ID AD_ASST_SALARY MK_MAN_SALARY MK_REP_SALARY PU_CLERK_SALARY PU_MAN_SALARY HR_REP_SALARY
------------- -------------- ------------- ------------- --------------- ------------- -------------
           10           4400
           20                        13000          6000
           30                                                      13900         11000
           40                                                                                   6500

下面对pivot输入sum和count两列

SQL> with pivot_employees as (select department_id,job_id,salary from employees where department_id <= 40)
  2  select * from  pivot_employees
  3  pivot(
  4       sum(salary) as s,
  5       count(salary) as c
  6       for job_id
  7       in (
  8      'AD_ASST' as AD_ASST,
  9      'MK_MAN' as MK_MAN,
 10      'MK_REP' as MK_REP,
 11      'PU_CLERK' as PU_CLERK,
 12      'PU_MAN' as PU_MAN,
 13      'HR_REP' as HR_REP
 14       )
 15  )
 16  order by 1;

DEPARTMENT_ID  AD_ASST_S  AD_ASST_C   MK_MAN_S   MK_MAN_C   MK_REP_S   MK_REP_C PU_CLERK_S PU_CLERK_C   PU_MAN_S   PU_MAN_C  HR_REP_S HR_REP_C
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------
           10       4400          1                     0                     0                     0                     0                0
           20                     0      13000          1       6000          1                     0                     0                0
           30                     0                     0                     0      13900          5      11000          1                0
           40                     0                     0                     0                     0                     0      6500      1

上述实验中对JOB_ID的列举使用了IN,当枚举值较多的时候,子查询获取子集的方式在pivot的使用中不受支持,但是oracle支持返回为xml格式的数据。

SQL> col JOB_ID_XML for a100
SQL> select * from
  2    (select department_id,job_id,salary from employees where department_id <= 40)
  3  pivot xml(
  4       sum(salary)     
  5       for job_id      
  6       in (any)
  7       )
  8  order by 1;
此条目发表在SQL分类目录。将固定链接加入收藏夹。

One Response to select pivot

  1. Pingback引用通告: select unpivot | LEO Notes

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s