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?

Several [duplicates](http://stackoverflow.com/search?q=wm_concat+listagg) — Noel, Jun 26 '13 at 06:43
@Arjun did you find you solution. I have modified my answer to match for 10g. — Dinup Kandel, Jun 26 '13 at 08:04

score 4 · Answer 1 · answered Jun 26 '13 at 06:48

4

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

answered Jun 26 '13 at 06:48

shonky linux user

6,131
4
46
73

Dinup Kandel · Answer 2 · 2013-06-26T07:58:43.140

3

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

edited Jun 26 '13 at 07:58

answered Jun 26 '13 at 06:43

Dinup Kandel

2,457
4
21
38

2

`LISTAGG` is not supported in oracle10g – Noel Jun 26 '13 at 06:45
@Horrendous_Space_Kablooie yes thanks i thought it was 11g. now its done. – Dinup Kandel Jun 26 '13 at 07:54
Is it possible to turn this into a reusable function, to permit usage like... `select no, gconcat(name) from mytable;`? – Billy Moon Dec 29 '13 at 00:05

score 0 · Answer 3 · answered Jun 26 '13 at 06:47

0

you can use LISTAGG

see demo here

answered Jun 26 '13 at 06:47

chetan

2,876
1
14
15

This became available with 11g.2. – Jul 02 '14 at 21:51

score 0 · Answer 4 · answered Jun 26 '13 at 07:43

0

Try this SQL query

SELECT 
  [No],
  STUFF((
    SELECT ' ' + Name
    FROM #tbl_concat 
    WHERE ([No] = Results.[No]) 
    FOR XML PATH (''))
  ,1,0,'') AS NameValues
FROM #tbl_concat Results
GROUP BY [No]

answered Jun 26 '13 at 07:43

Msyma

57
1
5

STUFF keyword is great, thank @Msyma. – Frank Myat Thu Jul 05 '13 at 07:08
`FOR XML PATH` is `T-SQL` (Microsoft SQL Server) – ventiseis Dec 13 '16 at 08:36

Aggregate String Concatenation in Oracle 10g

4 Answers4

Linked