Aggregate String Concatenation in Oracle 10g [dupl

问题:

This question already has an answer here:

• Concatenate results from a SQL query in Oracle 7 answers

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?

回答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

回答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

回答3:

you can use LISTAGG

see demo here

回答4:

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]