Oracle: Combine multiple results in a subquery into a single comma-separated value [duplicate]
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.
There is an excellent summary of the available string aggregation techniques on Tim Hall's site.
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;