Concat all column values in sql

Question

How to concat all column values from differenct rows returned from a sql query into one value? This is an example:

a query returns:

 FOO ------ RES1  RES2  RES3 

now I want to have a result like the following one:

 FOOCONCAT ----- RES1RES2RES3 

Are there any ways to do this in sql?

Answer 1

In SQL Server:

SELECT  col1 AS [text()] FROM    foo FOR XML PATH ('') 

In MySQL:

SELECT  GROUP_CONCAT(col1 SEPARATOR '') FROM    foo 

In PostgreSQL:

SELECT  array_to_string         (         ARRAY         (         SELECT  col1         FROM    foo         ), ''         ) 

In Oracle:

SELECT  * FROM    (         SELECT  col1, ROW_NUMBER() OVER(ORDER BY 1) AS rn         FROM    foo         MODEL         DIMENSION BY                 (rn)         MEASURES                 (col1, col1 AS group_concat, 0 AS mark)         RULES UPDATE (                 group_concat[rn > 1] =  group_concat[CV() - 1] || col1[CV()],                 mark[ANY] = PRESENTV(mark[CV() + 1], 0, 1)                 )         ) WHERE   mark = 1 

Answer 2

Quassnoi's Oracle solution is quite impressive, but I found simpler ones using SYS_CONNECT_BY_PATH() rather than the MODEL magic.

SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(foo, '/')), '/', '') conc FROM (     SELECT T_FOO.*, ROW_NUMBER() OVER (ORDER BY FOO) R FROM T_FOO ) START WITH r=1 CONNECT BY PRIOR r = r-1;