sql: Aggregate / gather strings within one column (with a single query) [duplicate]

Question

Possible Duplicate:
Is there an Oracle SQL query that aggregates multiple rows into one row?
Fast way to generate concatenated strings in Oracle

An oracle sql newbie question

i have a following table:

id1 A
id1 B
id1 C
id1 A
id2 A
id3 B
id3 A

What I want to get

id1 A,B,C
id2 A
id3 B,A

I cannot use loops, i have to get it with just a query

I use an Oracle DB v10 (I know it's important because of this).

hm I guess you wrongly used the "similar" word. To me you want to group as long as you get different values, and if X are the same, skipp them and keep A ( row 4+5 in your given rows) — Najzero
– Najzero, Commented Sep 3, 2012 at 15:07
@Lamak almost. Thank you very much anyway. The difference: I would like to now have any the duplicates in the right column. — ScepticalAstronaut
– ScepticalAstronaut, Commented Sep 3, 2012 at 15:41
I would also like to add a site with several possible solutions: here — ScepticalAstronaut
– ScepticalAstronaut, Commented Sep 3, 2012 at 15:47

djadmin · Accepted Answer · 2012-09-03 16:19:46Z

0

You need to use wm_concat(fieldname) to solve the purpose. So your query will be :

SELECT attr1, wm_concat(attr2) FROM YourTable GROUP BY field2;

And If you want Duplicates to be removed then

SELECT attr1, wm_concat(distinct attr2) FROM YourTable GROUP BY field2;

answered Sep 3, 2012 at 16:19

djadmin

1,7603 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wm_concat doesn't allow me to use several tables in one query...