Recipe12.16.Creating a Sparse Matrix

Recipe 12.16. Creating a Sparse Matrix

Problem

You want to create a sparse matrix, such as the following one transposing the DEPTNO and JOB columns of table EMP:

 D10        D20        D30        CLERKS MGRS  PREZ ANALS SALES ---------- ---------- ---------- ------ ----- ---- ----- ------            SMITH                 SMITH                       ALLEN                              ALLEN                       WARD                               WARD            JONES                        JONES                       MARTIN                             MARTIN                       BLAKE             BLAKE CLARK                                   CLARK            SCOTT                                   SCOTT KING                                          KING                       TURNER                             TURNER            ADAMS                 ADAMS                       JAMES      JAMES            FORD                                    FORD MILLER                           MILLER 

Solution

Use CASE expressions to create a sparse row-to-column transformation:

  1 select case deptno when 10 then ename end as d10,  2        case deptno when 20 then ename end as d20,  3        case deptno when 30 then ename end as d30,  4        case job when 'CLERK' then ename end as clerks,  5        case job when 'MANAGER' then ename end as mgrs,  6        case job when 'PRESIDENT' then ename end as prez,  7        case job when 'ANALYST' then ename end as anals,  8        case job when 'SALESMAN' then ename end as sales  9   from emp 

Discussion

To transform the DEPTNO and JOB rows to columns, simply use a CASE expression to evaluate the possible values returned by those rows. That's all there is to it. As an aside, if you want to "densify" the report and get rid of some of those NULL rows, you would need to find something to group by. For example, use the window function ROW_NUMBER OVER to assign a ranking for each employee per DEPTNO, and then use the aggregate function MAX to rub out some of the NULLs:

  select max(case deptno when 10 then ename end) d10,        max(case deptno when 20 then ename end) d20,        max(case deptno when 30 then ename end) d30,        max(case job when 'CLERK' then ename end) clerks,        max(case job when 'MANAGER' then ename end) mgrs,        max(case job when 'PRESIDENT' then ename end) prez,         max(case job when 'ANALYST' then ename end) anals,         max(case job when 'SALESMAN' then ename end) sales   from (  select deptno, job, ename,        row_number()over(partition by deptno order by empno) rn    from emp         ) x   group by rn D10        D20        D30        CLERKS MGRS  PREZ ANALS SALES ---------- ---------- ---------- ------ ----- ---- ----- ------ CLARK      SMITH      ALLEN      SMITH  CLARK            ALLEN KING       JONES      WARD              JONES KING       WARD MILLER     SCOTT      MARTIN     MILLER       SCOTT      MARTIN            ADAMS      BLAKE      ADAMS  BLAKE            FORD       TURNER                  FORD       TURNER                       JAMES      JAMES