A new approach to pivot queries in 11g R2

[karluk]

Pivot queries are a frequent topic in the Tek-tips Oracle forums. Thanks to taupirho's helpful tip from last year, we know how to write them using the sys_connect_by_path function, which is available in Oracle 10g R1 and above. See thread759-1492961

Starting in Oracle 11g, release 2, the "listagg" function simplifies the SQL coding of pivot queries even more. I see this function in an 11g R2 new features article by Tom Kyte:

http://www.oracle.com/technology/or...sktom.html?msgid=8284103&eid=3897823119&lid=1

Quoting from the article:

Oracle9i Database Release 1 introduced user-defined aggregate functions, and the first user-defined aggregate I wrote, called STRAGG, created a delimited list within a group (an aggregate—see

http://www.bit.ly/ozjv6).

It was about 100 lines of code and required an object type and an aggregate mapping function—lots of stuff.

When Oracle Database 10g Release 1 came out, we could use the new SYS_CONNECT_BY_PATH function to accomplish the same capability in pure SQL without having to resort to lots of procedural code. It was “pure SQL,” but it was complex.

Now, in Oracle Database 11g Release 2, we can accomplish the “stringing up of a list” very easily:


SQL> select deptno,
2 listagg( ename, '; ' )
3 within group
4 (order by ename) enames
5 from emp
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

 

[SantaMufasa]

And, for the 10g folks, you can use this equally simple code:

select dept_id,wm_concat(last_name) names
  from s_emp
 group by dept_id;

 DEPT_ID NAMES
-------- --------------------------
      10 Quick-To-See
      31 Nagayama,Magee
      32 Giljum
      33 Sedeghi
      34 Nguyen,Patel
      35 Dumas
      41 Ngao,Smith,Urguhart,Maduro
      42 Menchu,Nozaki,Patel
      43 Biri,Newman,Markarian
      44 Catchpole,Chang
      45 Havel,Dancs,Schwartz
      50 Velasquez,Ropeburn

12 rows selected.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[taupirho]

Karluk,

I actually ran the original sys_connect_by_path code on:-

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production

So it does work on some pre-10 versions





In order to understand recursion, you must first understand recursion.