MYSQL Multidimensional Group_Concat based on 2 qualifiers

Question

Here is my data

I need to group by data_id and run a select query so that the data returns in this fashion, using data_id=2 as an example.

optics_finish:3673|optics_reticle:3923,3924

Using group_concat, I have been successful in getting all the data but it returns like this:

optics_finish:3673|optics_reticle:3923|optics_reticle:3924

But the thing is that I cannot repeat the attribute code. I need a group within a group and I keep getting the error Invalid use of a group function.

Thanks for your help!

Answer 1

It would be immensely helpful if you'd post the query you've used, but anyway... I think I was able to get the results you're looking for with the following query

SELECT data_id, GROUP_CONCAT(CONCAT_WS(':', attribute_code, IDs) SEPARATOR '|') AS concatMess
FROM 
(
    SELECT data_id, attribute_code, GROUP_CONCAT(attribute_id SEPARATOR ',') AS `IDs` 
    FROM data 
    WHERE 1 
    GROUP BY attribute_code
) sq 
GROUP BY data_id;

Note: The SEPARATOR ',' isn't really necessary, but I like to be explicit. Feel free to remove it

Results:

data_id    concatMess
   1    manufacturer:148
   2    optics_finish:3673|optics_reticle:3923,3924