Concat all column values in sql

Solution 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

Solution 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;

Solution 3:

The mysql way:

select group_concat(somecolumn separator '') from sometable

Solution 4:

Assuming that it's one column with multiple values, this approach works for MS SQL Server (I can't speak for other systems).

declare @result varchar(max)
set @result = ''

select @result = @result + RES
from (query goes here)

Solution 5:

Here is the answer you are looking for; I had a feeling the solution lay in the CONNECT BY operation, I just hadn't used the SYS_CONNECT_BY_PATH pseudocolumn before (which displays the full path to the node in a tree, separating node names by a "/"). Assuming that your set of "foo" values before are multiple rows in a table, grouped by a column "myKey", e.g.:

myKey    foo
-------- ----------
group 1  apple
group 1  orange
group 1  pear
group 2  ape
group 2  bear
group 2  kitten

you can treat the data as if it were a tree schema, and pretend that the values of each group represent nodes going down a branch. In that case, you'd do this:

  SELECT myKey
       , SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(foo, '/')
                           ,'/'
                           ,' '
                           )
                   )
               ,2
               ) FooConcat
    FROM ( SELECT MyKey
                , Foo
                , row_number() OVER (Partition by myKey order by myKey) NodeDepth
             FROM MyTable
         )
   START WITH NodeDepth = 1
 CONNECT BY PRIOR myKey = myKey
     AND PRIOR NodeDepth = NodeDepth -1
GROUP BY myKey
;

Of course, the order of the concatenated values would be random; if your table had another column ("bar") that you could use as an ordering field that was ascending and contiguous, you could dispense with the subquery (which only exists to put an imaginary depth to the tree) and use the table directly, replacing NodeDepth with bar.