MySQL - concat column rows as single string using SELECT query

2 contributors

7 contributions

0 discussions

8 points

Created by:

21020

In this short article, we would like to show how in MySQL, build a SELECT query that returns one string created from concated column rows.

Hint: below examples use built-in GROUP_CONCAT() function that joins indicated columns as a single string.

Quick solution:

SELECT GROUP_CONCAT(u.`name` SEPARATOR ' ')
FROM users u
WHERE u.`id` = 100

Result:

john matt ann

Returning column data as JavaScript array in JSON format:

SET @text:= (
	SELECT GROUP_CONCAT('"', t.name, '"')
	FROM users u
	WHERE u.`id` = 100
);

SELECT CONCAT('[', @text, ']');

Result:

["john","matt","ann"]

Note: GROUP_CONCAT() function uses coma as default separator.

Alternative titles