0%

Oracle PIVOT 語法範例

發表於 更新於 分類於 ，

上星期有同事問到 Oracle PIVOT 語法的問題，問題如下:

Pivot
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with t
as
(select dname, job, count(*) cnt
   from emp e, dept d
  where e.deptno = d.deptno
  group by dname, job
)
select *
  from t
       pivot (
         sum(cnt)
         for (job) in ('ANALYST', 'CLERK', 'SALESMAN')
      )
 order by dname
;

這得到的結果 :

Result
DNAME           'ANALYST'    'CLERK' 'SALESMAN'
-------------- ---------- ---------- ----------
OPERATIONS              1
RESEARCH                2          2
SALES                              1          3
會計部                   1          1
資訊部                   1

這裡的資料標頭 ‘ANALYST’ 多了 ‘ ‘ 單引號，同事問如何將它去除。

以下就是設定這些標頭的範例。

Pivot
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with t
as
(select dname, job, count(*) cnt
   from emp e, dept d
  where e.deptno = d.deptno
  group by dname, job
)
select *
  from t
       pivot (
         sum(cnt)
         for (job) in ('ANALYST' as Analyst, 'CLERK' as Clerk, 'SALESMAN' as Salesman)
      )
 order by dname
;

我只修改了第 12 行加入了 AS 語法，就像在 SELECT 語法中一樣。

Result
DNAME             ANALYST      CLERK   SALESMAN
-------------- ---------- ---------- ----------
OPERATIONS              1
RESEARCH                2          2
SALES                              1          3
會計部                   1          1
資訊部                   1

如果想區分大小寫或加入中文，則可以用雙引號 “Analyst “。

Pivot
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with t
as
(select dname, job, count(*) cnt
   from emp e, dept d
  where e.deptno = d.deptno
  group by dname, job
)
select *
  from t
       pivot (
         sum(cnt)
         for (job) in ('ANALYST' as "分析員", 'CLERK' as "營業員", 'SALESMAN' as "推銷員")
      )
 order by dname
;
Result
DNAME              分析員     營業員     推銷員
-------------- ---------- ---------- ----------
OPERATIONS              1
RESEARCH                2          2
SALES                              1          3
會計部                   1          1
資訊部                   1

也可以字串合併。

Pivot
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
with t
as
(select dname, job, sal
   from emp e, dept d
  where e.deptno = d.deptno
)
select *
  from t
       pivot (
         min(sal) as "最低薪",
         max(sal) as "最高薪",
         sum(sal) as "合計"
         for (job) in ('CLERK' as "營業員", 'SALESMAN' as "推銷員")
      )
 order by dname
;
Result
DNAME           營業員_最低薪   營業員_最高薪   營業員_合計   推銷員_最低薪   推銷員_最高薪   推銷員_合計
-------------- ------------- ------------- ----------- ------------- ------------- -----------
OPERATIONS
RESEARCH                1100          8001        9101
SALES                  94998         94998       94998          1250          1600        4350
會計部                   1500          1500        1500
資訊部