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

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, Sep 03 '12 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, Sep 03 '12 at 15:41
I would also like to add a site with several possible solutions: [here](http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php) — ScepticalAstronaut, Sep 03 '12 at 15:47

score 0 · Accepted Answer · answered Sep 03 '12 at 16:19

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 03 '12 at 16:19

djadmin

`wm_concat` doesn't allow me to use several tables in one query... – ScepticalAstronaut Sep 03 '12 at 17:59

1 Answers1