Search code examples
mysqlconcatenationcoalesce

How to merge a column from multiple rows into a single string

I have a table which has 2 rowscheck image now I would like to merge these rows and get a single string. something like this 'santhosh,santhosh'

I checked some examples suggesting to use COALESCE tried like this

set @col = '';
SELECT @col = COALESCE(@col + ',', '') + name into @col
  FROM cricketers limit 20;
select @col;

but I never got the expected results, how should I achieve this, I'm running this inside a procedure. i would like to use the variable @col for doing a query like this

select * from table where id in (@col)

if I'm not following the correct process please suggest something.

Solution
    1. Coalesce simply allows to replace NULL with another value. It cannot solve your task.
    2. + does not concatenate strings, it is arithmetic addition operator only.
    3. If you need to concatenate values from a lot of rows into single value then you should use GROUP BY (maybe implicit) and GROUP_CONCAT():
    SELECT GROUP_CONCAT([DISTINCT] name [ORDER BY name])
FROM cricketers;

    If you do not need to remove duplicates then remove DISTINCT.

    If you need to limit the amount of values concatenated then you may concatenate then remove excess values by:

    SELECT SUBSTRING_INDEX(GROUP_CONCAT([DISTINCT] name [ORDER BY name]), ',', 20)
FROM cricketers;

    or select needed rows amount in subquery:

    SELECT GROUP_CONCAT(name [ORDER BY name])
FROM ( SELECT [DISTINCT] name
       FROM cricketers
       [ORDER BY name]
       LIMIT 20 ) subquery;

    ORDER BY expression presence is strongly recommended - without them you will receive indefinite values ordering (and in the case of amount limitation - their selection).