StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Oracle 11g has this neat function LISTAGG that is pretty much what you want, however since you are on 10g this is not available to you (unless you decide to upgrade).</p> <p>If for some reason you do not wish to (or can't due to whatever reasons) upgrade to 11g, I'd suggest looking at some alternatives to LISTAGG that are available to you on 10g.</p> <p>You can check out some of the proposed alternatives <a href="http://www.dautkhanov.com/2010/08/listagg-alternative-in-10g.html" rel="nofollow" title="10g LISTAGG Alternatives">here</a></p> <p>Quickly adjusted a quick adaptation of one of the proposed alternatives to match your case scenario:</p> <pre><code>WITH Q AS ( SELECT 'North' POD, 'Rony' NAME FROM DUAL UNION ALL SELECT 'North', 'James' FROM DUAL UNION ALL SELECT 'North', 'Aby' FROM DUAL UNION ALL SELECT 'South', 'Sam' FROM DUAL UNION ALL SELECT 'South', 'Willy' FROM DUAL UNION ALL SELECT 'West', 'Mike' FROM DUAL ) SELECT POD, RTRIM( XMLAGG (XMLELEMENT(e, name||',') ORDER BY name).EXTRACT('//text()'), ',' ) AS name FROM q GROUP BY POD; </code></pre> <p>But remember that this is not the actual solution as you'll have to tailor it according to your table (not the dummy DUAL table) etc...</p> <p>Your solution will probably look something along the lines of:</p> <pre><code>SELECT POD, RTRIM( XMLAGG (XMLELEMENT(E, NAME||',') ORDER BY NAME).EXTRACT('//text()'), ',' ) AS NAME FROM tbl1 GROUP BY POD; </code></pre> <p>If you want to change the delimiter you can change it from comma in this part:</p> <pre><code>(E, NAME||',') </code></pre> <p>The RTRIM is there just to snip off the trailing comma from the end of the concatenated string, if you are not bothered by the trailing comma you can omit the RTRIM function to conserve readability.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload