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.
This question is related to
mysql
concat
group-concat
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
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
SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result
FROM test GROUP BY ID
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;
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
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 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.
Source: Stackoverflow.com