Join 110 other subscribers
Abstract Confusions
Complexity is not a cause of confusion. It is a result of it.
Tag Archives: Oracle 11g R2
LISTAGG and PIVOT functions in Oracle 11g R2
Analytic functions are first introduced in Oracle 8i. They have got few functions in new release. The first function which I will discuss here will be LISTAGG. It creates a delimited list within a group. Some times you need to toil hard for this. Most of the times, grouping by and converting columns to rows.
With Oracle 11g R2, all you need is the following.
SQL> select deptno, 2 listagg( ename, '; ' ) 3 within group 4 (order by ename) enames 5 from hr.employees 6 group by deptno 7 order by deptno 8 / DEPTNO ENAMES --------- -------------------- 10 CLARK; KING; MILLER 20 ADAMS; FORD; JONES; SCOTT; SMITH 30 ALLEN; BLAKE; JAMES; MARTIN; TURNER; WARD
You can decide what to append with. The syntax is LISTAGG(column, character). Read more of this post
13.060416
80.249634