Transpose Rows to Columns

In the old Oracle 8i days, transposing rows to columns was not straight forward. As Oracle Developer (working on Oracle Developer 2000), I remember writing a query using MAX and DECODE functions to transpose rows into columns for my previous employer. My efforts were applauded as this query replaced the old database stored procedure (function). Thanks to Tom Kyte for his wonderful asktom.oracle.com site.

Here’s a sample function which was then in use:

SQL> create or replace function test_func(p_deptno in emp.deptno%type)
  2    return varchar2 is
  3    cursor emp_cur is
  4       select deptno, ename
  5         from emp
  6        where deptno = nvl(p_deptno, deptno)
  7        order by ename;
  8    l_ename varchar2(500);
  9  begin
 10    for emp_rec in emp_cur loop
 11      l_ename := l_ename ||','|| emp_rec.ename;
 12    end loop;
 13    return(substr(l_ename, 2));
 14  end;
 15  /

Function created.

SQL>
SQL> column new_col format a50
SQL>
SQL>
SQL> select test_func(10) new_col from dual;

NEW_COL
--------------------------------------------------
CLARK,KING,MILLER

SQL> select test_func(20) new_col from dual;

NEW_COL
--------------------------------------------------
ADAMS,FORD,JONES,SCOTT,SMITH

SQL> select test_func(30) new_col from dual;

NEW_COL
--------------------------------------------------
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
SQL> select deptno, test_func(deptno) new_col
  2    from (select distinct deptno from emp)
  3    order by deptno;

    DEPTNO NEW_COL
---------- -----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
SQL>

I stumbled upon Analytic Functions that were introduced with Oracle 8i and came up with the following query:

SQL> select deptno, rtrim(new_col, ',') new_col
  2    from (select deptno,
  3                 max(decode ( rn , 1, ename || ',')) ||
  4                 max(decode ( rn , 2, ename || ',')) ||
  5                 max(decode ( rn , 3, ename || ',')) ||
  6                 max(decode ( rn , 4, ename || ',')) ||
  7                 max(decode ( rn , 5, ename || ',')) ||
  8                 max(decode ( rn , 6, ename )) new_col
  9            from (select ename, deptno,
 10                         row_number() over ( partition by deptno
 11                                             order by rownum) rn
 12                    from emp)
 13            group by deptno)
 14  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

SQL>

The major drawback of the above query is the limitation of number of values it can display for the ENAME column. If a new employee joins department number “30” then this query has to be modified. This query was well suited for the environment where it was written as the number of values was known and were limited.

Then came Oracle 9i introducing SYS_CONNECT_BY_PATH function. The above limitations are overcome using this new function. It is capable of transposing “n” number of rows into columns. The same query can now be rewritten as:

SQL>
SQL> select deptno,
  2         max(substr(sys_connect_by_path (ename, ','),2)) new_col
  3   from (select deptno, ename,
  4                row_number() over (partition by deptno
  5                                    order by ename) rno
  6          from emp)
  7  start with rno = 1
  8  connect by rno = prior rno + 1
  9      and prior deptno = deptno
 10  group by deptno
 11  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

Well, so far so good. But Oracle 11g introduced LISTAGG function. This function makes life very easy. The query looks very simple, no hierarchies, no connect by ….

Here’s the new query:

SQL> 
SQL> select deptno,
  2         listagg(ename, ',') within group 
  3            (order by ename) new_col
  4    from emp
  5  group by deptno
  6  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

Isn’t it simple? Over the years Oracle has introduced tons of new features (like the one discussed here) that make life simple and at the same time making the code more performant.

The question is "are we really using these features efficiently?"