How to use GROUP_CONCAT in 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 in the following format?

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

I think I need to use GROUP_CONCAT , but I'm not sure how this works.

Answer 1

 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 how this is implemented here: Sql Fiddle demo . Exactly what you need.

Update Separation in two steps. First we get a table with all the values ​​(separated by commas) against the unique [Name, id]. Then, from the resulting table, we get all the names and values ​​as one value for each unique identifier. See here for an explanation of the SQL Fiddle demo (scroll down, because it has two sets of results)

Change There was an error in the reading question, I grouped it only by id. But two group_contacts are necessary if (values ​​must be combined, grouped by name and id, and then all by id). Previous answer was

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

You can see how this is implemented here: SQL Fiddle Demo

Answer 2

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

Answer 3

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

Answer 4

First of all, I don’t see the reason for having a unique identifier, but I think it is an identifier that connects to another table. Secondly, there is no need for subqueries that are superior to the server. You do this in one request, for example

 SELECT id,GROUP_CONCAT(name, ':', value SEPARATOR "|") FROM sample GROUP BY id 

You get fast and correct results, and you can split the result into this SEPARATOR "|". I always use this delimiter because it is impossible to find it inside a string, therefore it is unique. There is no problem with two A, you define only the value. Or you may have another copy with a letter, which is even better. Like this:

 SELECT id,GROUP_CONCAT(DISTINCT(name)), GROUP_CONCAT(value SEPARATOR "|") FROM sample GROUP BY name 

Answer 5

  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 BLOB will be returned

result

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

you will have to process the result again with a program like python or java

Answer 6

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,''),'<Value>', ''),'</Value>','') from test _a order by 1 desc 

My table name is a test, and for concatenation, I use the For XML Path ('') syntax. The stuff function inserts a string into another string. It deletes the specified length of characters in the first line at the starting position, and then inserts the second line into the first line at the starting position.

The STUFF functions are as follows: STUFF (character_expression, start, length, character_expression)

character_expression Is an expression of character data. character_expression can be a constant, a variable, or a column of character or binary data.

start Is an integer value indicating the location to start deleting and pasting. If the start or length is negative, a null string is returned. If the beginning is larger than the first character_expression expression, an empty string is returned. start may be of type bigint.

Length Is an integer specifying the number of characters to delete. If the length is greater than the first character_expression expression, deletion occurs before the last character in the last character expression. length can be bigint type.