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

I have a table which has 2 rows 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.

CodePudding user response：

Coalesce simply allows to replace NULL with another value. It cannot solve your task.
does not concatenate strings, it is arithmetic addition operator only.
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).