user1859440 user1859440 - 2 months ago 22
MySQL Question

Mysql transpose and group by

I would like to make a sort of transpose/groupby. I explain my case.

I have a table which look like that

p_qualifier | p_value
-----------------------------------------------------|------------
|mooClassification/1.0/class-4.spherecorrectionlist |+0.75
|mooClassification/1.0/class-4.spherecorrectionlist |+1.00
|mooClassification/1.0/class-4.spherecorrectionlist |+1.25
|mooClassification/1.0/class-4.spherecorrectionlist |+1.50
|mooClassification/1.0/class-4.spherecorrectionlist |+1.75


And I would like to obtain by a query (but i don't know if it's possible) something like that

p_qualifier | p_value
-----------------------------------------------------|---------------------------------
|mooClassification/1.0/class-4.spherecorrectionlist |+0.75; +1.00; +1.25; +1.50; +1,75


Thanks inadvance for your help.

Answer

You can use GROUP_CONCAT:

SELECT p_qualifier, GROUP_CONCAT(p_value ORDER BY p_value ASC SEPARATOR '; ') AS `p_value`
FROM table
GROUP BY p_qualifier

It will group column p_qualifier (by same rows) and GROUP_CONCAT will concat the result set of entire GROUP separating by the SEPARATOR.