How to use GROUP CONCAT in a CONCAT in MySQL

Question

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

User · Answer

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

User · Answer

SELECT ID  GROUP CONCAT CONCAT WS      NAME  VALUE  SEPARATOR      AS Result  FROM test GROUP BY ID

User · Answer

IF OBJECT ID  master  test   is not null Drop table test  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 distinct NAME   LIST   Replace Replace Stuff  select       Value from test where name    a name for xml path       1 1       lt Value gt          lt  Value gt       from test  a order by 1 desc   My table name is test   and for concatination I use the For XML Path     syntax  The stuff function inserts a string into another string  It deletes a specified length of characters  in the first string at the start position and then inserts the second string into the first string   at the start position   STUFF functions looks like this   STUFF  character expression   start   length  character expression     character expression  Is an expression of character data  character expression can be a constant  variable  or column of either  character or binary data   start  Is an integer value that specifies the location to start deletion and insertion  If start or length is negative   a null string is returned  If start is longer than the first character expression  a null string is returned   start can be of type bigint   length  Is an integer that specifies the number of characters to delete  If length is longer than the first character expression   deletion occurs up to the last character in the last character expression  length can be of type bigint

User · Answer

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   you have to handle result once again by program such as python or java

User · Answer

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

User · Answer

First of all  I don t see the reason for having an ID that s not unique  but I guess it s an ID that connects to another table  Second there is no need for subqueries  which beats up the server  You do this in one query  like this  SELECT id GROUP CONCAT name       value SEPARATOR      FROM sample GROUP BY id   You get fast and correct results  and you can split the result by that SEPARATOR      I always use this separator  because it s impossible to find it inside a string  therefor it s unique  There is no problem having two A s  you identify only the value  Or you can have one more colum  with the letter  which is even better  Like this    SELECT id GROUP CONCAT DISTINCT name    GROUP CONCAT value SEPARATOR      FROM sample GROUP BY name

User · Answer

SELECT id          Group concat column   FROM    SELECT id                  Concat name       Group concat value   AS column          FROM   mytbl          GROUP  BY id                     name  tbl  GROUP  BY id

[mysql] How to use GROUP_CONCAT in a CONCAT in MySQL

Examples related to mysql

Examples related to concat

Examples related to group-concat