Add comma-separated value of grouped rows to existing query

Question

I've got a view for reports, that looks something like this:

SELECT
  a.id,
  a.value1,
  a.value2,
  b.value1,
  /* (+50 more such columns)*/
FROM a
JOIN b ON (b.id = a.b_id)
JOIN c ON (c.id = b.c_id)
LEFT JOIN d ON (d.id = b.d_id)
LEFT JOIN e ON (e.id = d.e_id)
/* (+10 more inner/left joins) */

It joins quite a few tables and returns lots of columns, but indexes are in place and performance is fine.

Now I want to add another column to the result, showing

• comma-separated values
• ordered by value
• from table y
• outer joined via intersection table x
• if a.value3 IS NULL, else take a.value3

To comma-separate the grouped values I use Tom Kyte's stragg, could use COLLECT later.

Pseudo-code for the SELECT would look like that:

SELECT xx.id, COALESCE( a.value3, stragg( xx.val ) ) value3
FROM (
  SELECT x.id, y.val
  FROM x
  WHERE x.a_id = a.id
  JOIN y ON ( y.id = x.y_id )
  ORDER BY y.val ASC
) xx
GROUP BY xx.id

What is the best way to do it? Any tips?

Answer 1

Oracle 11.2.x has a new function LISTAGG. Maybe this new function is arriving too late for you?

example (From TK in Ora Mag):

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;

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH

  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

Answer 2

hi!

u can just concatenate all the columns, using ||','||. for ur query,

a.id||','|| a.value1||','|| a.value2||','|| b.value1||','|| /* (+50 more such columns)*/ FROM a JOIN b ON (b.id = a.b_id) JOIN c ON (c.id = b.c_id) LEFT JOIN d ON (d.id = b.d_id) LEFT JOIN e ON (e.id = d.e_id); this wil give u commas in the .csv format. or u can use any tool like dbvisualizer or execute qyery.