Using CASE and GROUPING() to Convert Multiple Column Values

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> SELECT
  2   CASE GROUPING(deptno)
  3  WHEN 1 THEN 'All divisions'
  4  ELSE 'else'
  5  END AS div,
  6  CASE GROUPING(job)
  7  WHEN 1 THEN 'All jobs'
  8  ELSE 'elseJob'
  9  END AS job,
 10  SUM(sal)
 11  FROM emp
 12  GROUP BY ROLLUP(deptno, job)
 13  ORDER BY deptno, job;

DIV           JOB        SUM(SAL)
------------- -------- ----------
else          All jobs       5850
else          elseJob        3000
else          elseJob        2850
else          All jobs       8275
else          elseJob        3000
else          elseJob        2300
else          elseJob        2975
else          All jobs       8450
else          elseJob        2850
else          elseJob        5600
All divisions All jobs      22575

11 rows selected.

SQL>