I've a table xyz with the below content. And I need to query to get the output as given below. Thank you in advance for the answer.
table xyz ===== id name = ===== 1 a 2 b 3 c 1 d 4 h 2 i 1 j 3 k 1 l 7 e 2 f
the output shoud be
id name = ==== 1 a,d,j,l 2 b,i,f 3 c,k 4 h 7 e
Answer by Leigh Riffel ·
The 11.2 method would be to use the LISTAGG function as follows:
SELECT id, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) names FROM xyz GROUP BY id ORDER BY id;
Older versions have several not as good options. http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php provides a good summary of your options and stackoverflow.com/questions/1757394/oracle-way-to-aggregate-concatenate-an-ungrouped-column-in-grouped-results includes a few more.