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?"
