Difference between revisions of "Snippets"
From dbawiki
(Created page with "===Create a comma separated list of columns from a select statement=== Method 1: <pre> SELECT parent_id, RTRIM(XMLAGG(XMLELEMENT(e,child_id || ',')).EXTRACT('//text()')...") |
(→Create a comma separated list of columns from a select statement) |
||
| Line 25: | Line 25: | ||
/ | / | ||
</pre> | </pre> | ||
| + | |||
| + | ====References==== | ||
| + | [http://stackoverflow.com/questions/5324996/comma-separated-list-as-a-result-of-select-statement-in-oracle stackoverflow.com] | ||
Revision as of 13:13, 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> /
