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

Question

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.

select * from table where id in (@col) won't work id will be compared to 'santhosh,santhosh' in it;s entirety not to each element in the string. — P.Salmon
– P.Salmon, Commented Oct 29, 2021 at 8:11

Akina · Accepted Answer · 2021-10-29 08:11:02Z

3

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

answered Oct 29, 2021 at 8:11

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

santhosh Over a year ago

if we want to limit the number of cricketers added each time is it possible?

Akina Over a year ago

@santhosh If you need to obtain a lot of concatenated values where each value matches some time range then you must use explicit GROUP BY with according expression. In this case only the solution with the subquery is applicable.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related