30 July 2014

Oracle PIVOT function sample script

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

SELECT job
,      deptno
,      SUM(sal) AS sum_sal
FROM   emp
GROUP  BY
       job
,      deptno
ORDER  BY
       job
,      deptno;

WITH pivot_data AS (
        SELECT deptno, job, sal
        FROM   emp
        )
SELECT *
FROM   pivot_data
PIVOT (
           SUM(sal)        --<-- p="" pivot_clause="">       FOR deptno          --<-- p="" pivot_for_clause="">       IN  (10,20,30,40)   --<-- p="" pivot_in_clause="">      );

SELECT *
FROM   emp
PIVOT (SUM(sal)
FOR    deptno IN (10,20,30,40));

WITH pivot_data AS (
        SELECT deptno, job, sal
        FROM   emp
        )
SELECT *
FROM   pivot_data
PIVOT (
           SUM(sal)        --<-- p="" pivot_clause="">       FOR deptno          --<-- p="" pivot_for_clause="">       IN  (10,20,30,40)   --<-- p="" pivot_in_clause="">      )
WHERE  job IN ('ANALYST','CLERK','SALESMAN');

SELECT *
FROM  (
       SELECT deptno, job, sal
       FROM   emp
      )
PIVOT (SUM(sal)
FOR    deptno IN (10,20,30,40));





set echo on

SELECT *
FROM   pivot_data
PIVOT (SUM(sal) AS salaries
FOR    deptno IN (10, 20, 30, 40));

SELECT *
FROM   pivot_data
PIVOT (SUM(sal) AS salaries
FOR    deptno IN (10 AS d10_sal,
                  20 AS d20_sal,
                  30 AS d30_sal,
                  40 AS d40_sal));

SELECT *
FROM   pivot_data
PIVOT (SUM(sal)
FOR    deptno IN (10 AS d10_sal,
                  20 AS d20_sal,
                  30 AS d30_sal,
                  40 AS d40_sal));

SELECT *
FROM   pivot_data
PIVOT (SUM(sal)
FOR    deptno IN (10 AS d10_sal,
                  20,
                  30 AS d30_sal,
                  40));

SELECT *
FROM   pivot_data
PIVOT (SUM(sal)   AS sum
,      COUNT(sal) AS cnt
FOR    deptno IN (10 AS d10_sal,
                  20 AS d20_sal,
                  30 AS d30_sal,
                  40 AS d40_sal));

SELECT *
FROM   pivot_data
PIVOT (SUM(sal)   AS sum
,      COUNT(sal) AS cnt
FOR   (deptno,job) IN ((30, 'SALESMAN') AS d30_sls,
                       (30, 'MANAGER')  AS d30_mgr,
                       (30, 'CLERK')    AS d30_clk));

SELECT d30_mgr_sum
,      d30_clk_cnt
FROM   pivot_data
PIVOT (SUM(sal)   AS sum
,      COUNT(sal) AS cnt
FOR   (deptno,job) IN ((30, 'SALESMAN') AS d30_sls,
                       (30, 'MANAGER')  AS d30_mgr,
                       (30, 'CLERK')    AS d30_clk));

No comments: