ansaurus

Question

Oracle: Combine multiple results in a subquery into a single comma-separated value

Answer 1

+1 A:

Here's a blog that shows an Oracle query to work like MySQL's GROUP_CONCAT():

http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html

Bill Karwin 2009-01-29 17:23:49

link to the original: http://tkyte.blogspot.com/2006/08/evolution.html

jimmyorr 2009-01-30 14:55:25

Answer 2

+3 A:

I found this that seems to work. Thoughts?

SELECT SUBSTR (c, 2) concatenated
  FROM (SELECT     SYS_CONNECT_BY_PATH ( myfield, ',') c, r
              FROM (SELECT   ROWNUM ID, myfield,
                             RANK () OVER (ORDER BY ROWID DESC) r
                        FROM mytable
                    ORDER BY myfield)
        START WITH ID = 1
        CONNECT BY PRIOR ID = ID - 1)
 WHERE r = 1;

Jason Cohen 2009-01-29 17:49:47

Doesn't that break if your ID values are not continuous?

Bill Karwin 2009-01-29 17:51:05

hmmm, yes I think you're right. :-(

Jason Cohen 2009-01-29 18:59:47

I'm not sure why you have both a ROWNUM and a RANK() in your inner query. You probably only need the RANK() and then START WITH r = 1 CONNECT BY PRIOR r = r - 1. This doesn't require continuous ID's, since both the ID and the rank are getting generated.

Justin Cave 2009-01-29 19:15:03

You do, however, probably want to use ROW_NUMBER() rather than RANK() in general. In this case it doesn't matter because ROWID is unique, but if you are ordering on a column that can potentially be non-unique, you don't want multiple rows with the same rank.

Justin Cave 2009-01-29 19:16:29

Answer 3

+2 A:

There is an excellent summary of the available string aggregation techniques on Tim Hall's site.

Justin Cave 2009-01-29 19:19:57

Answer 4

A:

11.2 introduced LISTAGG, which unlike WM_CONCAT is documented. We are not on 11.2 yet, so we use a custom aggregate function.

Leigh Riffel 2010-02-03 15:30:40

Answer 5

A:

SELECT deptno, wm_concat(ename) AS employees FROM emp GROUP BY deptno;

Reference: http://forums.oracle.com/forums/thread.jspa?messageID=1186901&#1186901

kalyan 2010-08-25 19:44:10

ansaurus

tags:

views:

answers:

Oracle: Combine multiple results in a subquery into a single comma-separated value

related questions