Difference between revisions of "Snippets"
From dbawiki
(→Create a comma separated list of columns from a select statement) |
(→Create a comma separated list of columns from a select statement) |
||
| Line 28: | Line 28: | ||
====References==== | ====References==== | ||
[http://stackoverflow.com/questions/5324996/comma-separated-list-as-a-result-of-select-statement-in-oracle stackoverflow.com] | [http://stackoverflow.com/questions/5324996/comma-separated-list-as-a-result-of-select-statement-in-oracle stackoverflow.com] | ||
| + | [http://www.salvis.com/blog/?p=207 A whole page showing how to do it in all versions back to 7!] | ||
Revision as of 13:48, 27 June 2013
Create a comma separated list of columns from a select statement
Method 1:
SELECT parent_id,
RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()'),',') AS "Children"
FROM parentChildTable
WHERE parent_id = 0
GROUP BY parent_id
/
or
SELECT parent_id,
LISTAGG(child_id, ',') WITHIN GROUP (ORDER BY child_id) AS "Children"
FROM parentChildTable
WHERE parent_id = 0
GROUP BY parent_id
/
Method 2 (undocumented and cannot therefore be relied on to continue working in the same manner):
SELECT wmsys.wm_concat(<column_name>) FROM <table_name> /
References
stackoverflow.com A whole page showing how to do it in all versions back to 7!
