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

select cast(res1 as varchar)+cast(res2 as varchar)+cast(res3 as varchar) as fooconcat from foo

If the columns are already strings you do not need the cast, you can just do:

select res1 + res2 + res3  as fooconcat from foo

For data from multiple rows, use PIVOT.

Answer 2

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 3

Concatenating strings depends on the database you are using (you havnt mentioned what version in your question so here goes)...

In Oracle and DB2 you can use the CONCAT function... CONCAT(string, string)

SQL Server you can use the '+' operator... string1 + string2 + string3

In MySQL it is CONCAT(string, string... n_string)

Finally in PostgreSQL it is TEXTCAT(string, string)...

...I got this out of this little cool book I have sitting on my desk SQL Pocket Guide from O'Reilly... check it out!

:)

Answer 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)

Answer 5

It might not be what you're looking for, but I've had good luck in the past with constructions like this:

SELECT      MAX(DECODE(fookey, 1, foo, NULL))
         || MAX(DECODE(fookey, 2, foo, NULL))
         || MAX(DECODE(fookey, 3, foo, NULL))
         || MAX(DECODE(fookey, 4, foo, NULL))
       , groupingvalue
    FROM mytable
GROUP BY groupingvalue;

It's platform independent, and it works well when you have a arbitrary, but limited number of values for foo, and they're based on some other key value. For example, if you have a table of invoices, and you want to see all the line times from the invoice on a single row, concatenated, and you have an upper limit of 5 line items, it would look like this:

SELECT      MAX(DECODE(lineno, 1, foo, NULL))
         || ', '
         || MAX(DECODE(lineno, 2, foo, NULL))
         || ', '
         || MAX(DECODE(lineno, 3, foo, NULL))
         || ', '
         || MAX(DECODE(lineno, 4, foo, NULL))
         || ', '
         || MAX(DECODE(lineno, 5, foo, NULL))
       , invoiceid
    FROM lineitem
GROUP BY invoiceid;

Answer 6

The mysql way:

select group_concat(somecolumn separator '') from sometable

Answer 7

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.

Answer 8

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;

Answer 9

Select ([col1] +','+[col2]+','+ [col3]+','+[col4]) as [MyCol] From [Table]