Aggregate String Concatenation in Oracle 10g

Question

I'm using Oracle 10 g, I have a scenario similar to this:

No Name
-- -----
1 Rony
1 James
1 Aby
2 Sam
2 Willy
3 Mike

I need to aggregate and concatenate the strings (with a single space in between), in a way to get the results:

No Name
-- -----
1 Rony James Aby
2 Sam Willy
3 Mike

I'm using Oracle 10g and have to implement this using SQL and not PL/SQL. Is there a way out?

Solution 1

It is easy on 11G, you can use the LISTAGG function, but sadly not on 10G

There are some techniques here for earlier versions however they do require a function to be written.

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

Solution 2

Try this query

select No  , rtrim(Name,',') Name
   from ( select No , Name , rn from yourtable
           model
  partition by (No)
  dimension by (row_number() over
 (partition by No order by Name) rn
 )
 measures  (cast(Name as varchar2(40)) Name)
   rules
 ( Name[any] order by rn desc = Name[cv()]||' '||Name[cv()+1]
   )
  )
    where rn = 1
    order by NO

Here is your sql demo

Comments

[Dinup Kandel]

@Arjun did you find you solution. I have modified my answer to match for 10g.

[Noel]

LISTAGG is not supported in oracle10g

[Dinup Kandel]

@Horrendous_Space_Kablooie yes thanks i thought it was 11g. now its done.

[Frank Myat Thu]

STUFF keyword is great, thank @Msyma.

[Billy Moon]

Is it possible to turn this into a reusable function, to permit usage like... select no, gconcat(name) from mytable;?

[Admin]

This became available with 11g.2.