10

I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values.

This is identical to this question, but for Oracle rather than SQL Server or MySQL.

Community
  • 1
  • 1
Jason Cohen
  • 81,399
  • 26
  • 107
  • 114
  • http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row – derobert Feb 03 '11 at 21:37

4 Answers4

14

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

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
5

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
  • 81,399
  • 26
  • 107
  • 114
  • Doesn't that break if your ID values are not continuous? – Bill Karwin Jan 29 '09 at 17:51
  • hmmm, yes I think you're right. :-( – Jason Cohen Jan 29 '09 at 18:59
  • 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 Jan 29 '09 at 19:15
  • 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 Jan 29 '09 at 19:16
3

11.2 introduced LISTAGG, which unlike WM_CONCAT is documented. A custom aggregate function could also do this.

Leigh Riffel
  • 6,381
  • 3
  • 34
  • 47
0

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

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

bluwater2001
  • 7,829
  • 5
  • 24
  • 21