Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 915
Biswa Avatar asked Nov 19 '12 10:11

Biswa


People also ask

What is the difference between concat and Group_concat in MySQL?

The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.

How do I concatenate two columns in SQL with a hyphen?

Do it with two concats: select concat(concat(amt, '-'), endamt) as amount from mstcatrule; concat(amt,'-') concatenates the amt with the dash and the resulting string is concatenated with endamt .

Can we use Group_concat in SQL Server?

The SQL Server Equivalent to GROUP_CONCAT() This function allows you to return a result set as a comma-separated list, as opposed to listing each row as a separate row (as with a normal result set).


3 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;

You can see it implemented here : Sql Fiddle Demo. Exactly what you need.

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 See this explained here SQL Fiddle Demo (scroll down as it has two result sets)

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

You can see it implemented here : SQL Fiddle Demo

like image 141
Sami Avatar answered Oct 18 '22 21:10

Sami


Try:

CREATE TABLE test (
  ID INTEGER,
  NAME VARCHAR (50),
  VALUE INTEGER
);

INSERT INTO test VALUES (1, 'A', 4);
INSERT INTO test VALUES (1, 'A', 5);
INSERT INTO test VALUES (1, 'B', 8);
INSERT INTO test VALUES (2, 'C', 9);

SELECT ID, GROUP_CONCAT(NAME ORDER BY NAME ASC SEPARATOR ',')
FROM (
  SELECT ID, CONCAT(NAME, ':', GROUP_CONCAT(VALUE ORDER BY VALUE ASC SEPARATOR ',')) AS NAME
  FROM test
  GROUP BY ID, NAME
) AS A
GROUP BY ID;

SQL Fiddle: http://sqlfiddle.com/#!2/b5abe/9/0

like image 27
eisberg Avatar answered Oct 18 '22 21:10

eisberg


SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result 
FROM test GROUP BY ID
like image 35
John Avatar answered Oct 18 '22 20:10

John