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));
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:
Post a Comment