Question: What is the Oracle listagg
function? I hear that there is a new way to pivot columns onto a
single display row?
Answer:
Oracle 11gR2 listagg built-in function allows for many
table columns to be displayed within a single row, a
non-first-normal form display:
select
deptno,
listagg
(ename, ',') WITHIN GROUP (ORDER BY ename)
enames
from
emp
group by
deptno;
DEPTNO
ENAMES
----------
--------------------------------------------------
10
CLARK,KING,MILLER
20
ADAMS,FORD,JONES,SCOTT,SMITH
30
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
Prior to the listagg function, we had
other SQL syntax for
converting rows to columns, including the old
sys_connect_by_path and the 11g pivot operator and within group
operators.
