MySQL Function: GROUP_CONCAT

Don’t know why I never thought to google for something like this before. There probably aren’t too many good uses for it, but it saved me a bunch of time today so I thought I’d share:

GROUP_CONCAT

Running a query like the one below, would return all the values from my sub-select as a comma delimited list so this…

SELECT products.namename,
        GROUP_CONCAT(options.option) AS options
FROM products
     INNER JOIN options
     ON          options.productID = products.productID

Could return something like this:

NAME	OPTIONS
t-shirt	green, blue, red, small, medium, large
hat	cowboy

MySQL DISTINCT on a GROUP_CONCAT()

I am doing SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table. 

Sample data below:

categories
----------
test1 test2 test3
test4
test1 test3
test1 test3

However, I am getting test1 test2 test3 test4 test1 test3 back and

 I would like to get test1 test2 test3 test4 back. Any ideas?

 Answers

GROUP_CONCAT has DISTINCT attribute:

SELECT GROUP_CONCAT(DISTINCT categories ORDER BY categories ASC 

SEPARATOR ' ') FROM table

---

Other answers to this question do not return what the OP needs, they will return 

a string like:

test1 test2 test3 test1 test3 test4

(notice that test1 and test3 are duplicated) while the OP wants to return this string:

test1 test2 test3 test4

the problem here is that the string "test1 test3" is duplicated and is inserted only once, 

but all of the others are distinct to each other ("test1 test2 test3" is distinct than

 "test1 test3", even if some tests contained in the whole string are duplicated).

What we need to do here is to split each string into different rows, and we first need to 

create a numbers table:

CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

then we can run this query:

SELECT
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(tableName.categories, ' ', numbers.n),
    ' ',
    -1) category
FROM
  numbers INNER JOIN tableName
  ON
    LENGTH(tableName.categories)>=
    LENGTH(REPLACE(tableName.categories, ' ', ''))+numbers.n-1;

and we get a result like this:

test1
test4
test1
test1
test2
test3
test3
test3

and then we can apply GROUP_CONCAT aggregate function, using DISTINCT clause:

SELECT
  GROUP_CONCAT(DISTINCT category ORDER BY category SEPARATOR ' ')
FROM (
  SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(tableName.categories, ' ', numbers.n), ' ', -1) category
  FROM
    numbers INNER JOIN tableName
    ON LENGTH(tableName.categories)>=LENGTH(REPLACE(tableName.categories, ' ', ''))+numbers.n-1
  ) s;

I realize this question is old, but I feel like this should be mentioned: group_concat 

with distinct = performance killer. If you work in small databases, you won't notice, 

but when it scales - it won't work very well.

How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL:

id       Name       Value
1          A          4
1          A          5
1          B          8
2          C          9

how do I get it into the following format?

id         Column
1          A:4,5,B:8
2          C:9

I think I have to use GROUP_CONCAT. But I'm not sure how it works.

 Answers

select id, group_concat(`Name` separator ',') as `ColumnName`
from
(
  select id, concat(`Name`, ':',
  group_concat(`Value` separator ',')) as `Name`
  from mytbl
  group by id, `Name`
) tbl
group by id;

Update Splitting in two steps. First we get a table having all values(comma separated) against a unique[Name,id]. Then from obtained table we get all names and values as a single value against each unique id 

Edit There was a mistake in reading question, I had grouped only by id. But two group_contacts are needed if (Values are to be concatenated grouped by Name and id and then over all by id). Previous answer was

select 
id,group_concat(concat(`name`,':',`value`) separator ',')
as Result from mytbl group by id

---

SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result 
FROM test GROUP BY ID

---

 SELECT id, GROUP_CONCAT(CONCAT_WS(':', Name, CAST(Value AS CHAR(7))) SEPARATOR ',') AS result 
    FROM test GROUP BY id

you must use cast or convert, otherwise will be return BLOB

result is

id         Column
1          A:4,A:5,B:8
2          C:9

MySQL and GROUP_CONCAT() maximum length

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.

I'm very well aware that I can change the param group_concat_max_len to increase this limit:

SET SESSION group_concat_max_len = 1000000;

However, on the server I'm using, I can't change any param. Not by using the preceding query and not by editing any configuration file.

So my question is: Is there any other way to get the output of a multiple row query into a single string?

 Answers

SET SESSION group_concat_max_len = 1000000;

is a temporary, session-scope, setting. It only applies to the current session You should use it like this.

SET SESSION group_concat_max_len = 1000000;
select group_concat(column) from table group by column

You can do this even in sharing hosting, but when you use an other session, you need to repeat the SET SESSION command.

---

Include this setting in xampp my.ini configuration file:

[mysqld] group_concat_max_len = 1000000

Then restart xampp mysql

---

The correct syntax is mysql> SET @@global.group_concat_max_len = integer;
If you do not have the privileges to do this on the server where your database resides then use a query like:
mySQL="SET @@session.group_concat_max_len = 10000;"or a different value.
Next line:
SET objRS = objConn.Execute(mySQL)  your variables may be different.
then
mySQL="SELECT GROUP_CONCAT(......);" etc
I use the last version since I do not have the privileges to change the default value of 1024 globally (using cPanel).

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like:

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

My Output:

shopping
fishing
coding

but instead I just want 1 row, 1 col:

Expected Output:

shopping, fishing, coding

The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.

I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone here know how to do this?

 Answers

You can use GROUP_CONCAT:

SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies GROUP BY person_id

 you can add the DISTINCT operator to avoid duplicates:

SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies GROUP BY person_id

you can also sort the values before imploding it using ORDER BY:

SELECT person_id, GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies GROUP BY person_id

there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
    (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
    FROM peoples_hobbies GROUP BY person_id)
    AS UNSIGNED
)

---

Alternate syntax to concatenate multiple, individual rows

WARNING: This post will make you hungry.

Given:

I found myself wanting to select multiple, individual rows—instead of a group—and concatenate on a certain field.

Let's say you have a table of product ids and their names and prices:

+------------+--------------------+-------+
| product_id | name               | price |
+------------+--------------------+-------+
|         13 | Double Double      |     5 |
|         14 | Neapolitan Shake   |     2 |
|         15 | Animal Style Fries |     3 |
|         16 | Root Beer          |     2 |
|         17 | Lame T-Shirt       |    15 |
+------------+--------------------+-------+

Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes.

Your hungry-hippo user selects 13, 15, 16. No dessert for her today...

Find:

A way to summarize your user's order in one line, with pure mysql.

Solution:

Use GROUP_CONCAT with the the IN clause:

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary FROM product WHERE product_id IN (13, 15, 16);

Which outputs:

+------------------------------------------------+
| order_summary                                  |
+------------------------------------------------+
| Double Double + Animal Style Fries + Root Beer |
+------------------------------------------------+

Bonus Solution:

If you want the total price too, toss in SUM():

mysql> SELECT GROUP_CONCAT(name SEPARATOR ' + ') AS order_summary, SUM(price) AS total FROM product WHERE product_id IN (13, 15, 16);
+------------------------------------------------+-------+
| order_summary                                  | total |
+------------------------------------------------+-------+
| Double Double + Animal Style Fries + Root Beer |    10 |
+------------------------------------------------+-------+

PS: Apologies if you don't have an In-N-Out nearby...

---

There's a GROUP Aggregate function, GROUP_CONCAT.

---

Use MySQL(5.6.13) session variable and assignment operator like the following

SELECT @logmsg := CONCAT_ws(',',@logmsg,items) FROM temp_SplitFields a;

then you can get

test1,test11

---

Try this:

DECLARE @Hobbies NVARCHAR(200) = ' '

SELECT @Hobbies = @Hobbies + hobbies + ',' FROM peoples_hobbies WHERE person_id = 5;

Mysql GROUP_CONCAT of first n rows || Selecting first and last values in a group || Select first and last row from group_concat when grouping sortable days

select s.id,count(e.id) as total,
substring_index(group_concat(e.id order by e.id asc),',',5) as group_value
from students s left join exams e on s.id=e.student_id
group by s.id
order by count(e.id) desc

MYSQL Group Concat Select Some Selected Rows Only

Its so simple. Just need to do below thins:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(x.id ORDER BY x.nx DESC), ',', 2) as row_name from some_table GROUP BY some_field

It will select First two values only.

It total value of GROUP_CONCAT is "1,2,3,4,5" Then Using SUBSTRING_INDEX would be like "1,2"

You can use DISTINCT in GROUP_CONCAT function like SUBSTRING_INDEX(DISTINCT(GROUP_CONCAT(x.id ORDER BY x.nx DESC)), ',', 2)

MySQL query with group contact

Let's say I have a table called "test" with the following design:

SELECT type, name, `key` FROM test

type | name    | key
------------------------
  0  | maria   | 123
  1  | gabriel | 455
  0  | rihanna | 69
  1  | chris   | 7
  1  | martin  | 112

The next query allows me to get all data in one line:

SELECT GROUP_CONCAT(type ORDER BY type) types, GROUP_CONCAT(name ORDER BY type) names, GROUP_CONCAT(`key` ORDER BY type) `keys` FROM test

  types   |               names                |      keys
------------------------------------------------------------------
0,0,1,1,1 | rihanna,maria,martin,chris,gabriel | 69,123,112,7,455

But that's not exactly what I need. It'd be perfect if I was able to create a query that returns the following result:

types_0 |     names_0    |  keys_0  | types_1 |         names_1         |    keys_1
------------------------------------------------------------------------------------
  0, 0  | maria, rihanna |  123, 69 |   1, 1  | gabriel, chris, martin  | 455, 7, 112

Is there any way to create such query? or wouldn't it even make sense at all?

Thanks in advance.

---

It is kind of possible but I wouldn't do it. It would look something like this:

SELECT * FROM
(
  SELECT
    GROUP_CONCAT(type ORDER BY type) types,
    GROUP_CONCAT(name ORDER BY type) names,
    GROUP_CONCAT(`key` ORDER BY type) `keys`
  FROM test
  WHERE type = 0
) AS _type0,
(
  SELECT
    GROUP_CONCAT(type ORDER BY type) types,
    GROUP_CONCAT(name ORDER BY type) names,
    GROUP_CONCAT(`key` ORDER BY type) `keys`
  FROM test
  WHERE type = 1
) AS _type1;

There is no way to generate more columns dynamically if it finds more types. This is typical of pivot table queries -- you must know the distinct values before you write the query.

I would instead do this:

SELECT
  type,
  GROUP_CONCAT(name ORDER BY name) names,
  GROUP_CONCAT(`key` ORDER BY name) `keys`
FROM test
GROUP BY type;

And the output should look like:

 type |         names        |  keys
------------------------------------------------------------------
 0    | maria,rihanna        | 123,69
 1    | chris,gabriel,martin | 7,455,112