CODEWITHSUNDEEP
mysqlconcatenationcoalesce
santhosh
santhosh

Reputation: 2008

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.

Upvotes: 1

Views: 1302

Answers (1)

Akina
Akina

Reputation: 42661

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

Upvotes: 3

Related Questions