CODEWITHSUNDEEP
mysqlgroup-concat
Carlos Eugenio Thompson Pinzón
Carlos Eugenio Thompson Pinzón

Reputation: 2668

How could I pass the separator as a variable in MySQL GROUP_CONCAT?

I would like to pass the SEPARATOR value of a GROUP_CONTACT as a variable (or function parameter), however this code will fail

SET @sep = ' ';
SELECT
    `group`,
    GROUP_CONCAT( `field` ORDER BY `idx` SEPARATOR @sep ) `fields`
FROM `table`
GROUP BY `group`;

I know I can do something like

SELECT
    `group`,
    SUBSTRING(
      GROUP_CONCAT( CONCAT(`field`,@sep) ORDER BY `idx` SEPARATOR ''),
      1,
      LENGTH(
        GROUP_CONCAT( CONCAT(`field`,@sep) ORDER BY `idx` SEPARATOR '')
      )-LENGTH(@sep)
    ) `fields`
FROM `table`
GROUP BY `group`;

But it would be nicer to have a more concise syntax.

Edit:

SELECT
    `group`,
    SUBSTRING(
      GROUP_CONCAT( CONCAT(@sep,`field`) ORDER BY `idx` SEPARATOR ''),
      LENGTH(@sep)+1
    ) `fields`
FROM `table`
GROUP BY `group`;

Is a little simpler, but not satisfactory enough.

Upvotes: 3

Views: 1530

Answers (2)

antoines
antoines

Reputation: 86

A very simple and straightforward solution is to use REPLACE after you have used GROUP_CONCAT. So, initially, the SEPARATOR could be just a comma, then you can replace the comma with the value of your parameter.

Upvotes: 0

Nick
Nick

Reputation: 147236

You could use a prepared statement:

SET @sep = '**';
SET @sql = CONCAT('SELECT `group`, GROUP_CONCAT( `field` ORDER BY `idx` SEPARATOR "',
@sep, '") `fields` FROM `table` GROUP BY `group`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I created a small demo on dbfiddle:

create table `table` (idx int auto_increment primary key, field varchar(10), `group` int);
insert into `table` (field, `group`) values
('hello', 4),
('world', 4),('today', 4),('hello', 3),('world', 3),
('hello', 5),('today', 5),('world', 5),('goodbye', 5)

Output of the prepared statement is:

group   fields
3       hello**world
4       hello**world**today
5       hello**today**world**goodbye

Upvotes: 1

Related Questions