Concatenate All Column Values in Sql

If you work with the SQL you might have came across this question sometime. How to Concatenate all column values from different rows returned from a SQL query into one value?

This is an example:

a query returns:

FOO
------
RES1

RES2

RES3

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

FOOCONCAT
-----
RES1RES2RES3

Here is the solution

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