MySQL: is it possible to group_concat multiple rows?

Question

Here's what I want:

attribute_name attribute_id attribute_value -------------------------------------------------------------------- Appliances 16, 17, 18, 19 Washer, Dryer, Dishwasher, Microwave Consoles 7, 3 PS3, XBox

Here's close to what I've got:

attribute_name attribute_id attribute_value ------------------------------------------------- Appliances 16 Washer Appliances 17 Dryer Appliances 18 Dishwasher Appliances 19 Microwave Consoles 7 PS3 Consoles 3 XBox

...from this query:

 SELECT a.name AS attribute_name, av.attribute_value_id, av.value AS attribute_value FROM attribute_value av INNER JOIN attribute a ON av.attribute_id = a.attribute_id WHERE av.attribute_value_id IN (SELECT attribute_value_id FROM property_attribute WHERE property_id = 1) ORDER BY a.name;

I've had no success with GROUP_CONCAT. I don't even know what I want is possible.

Please show the code you are actually having an issue with - the group_concat code. — Michael Durrant
– Michael Durrant, Commented Jun 28, 2012 at 2:54

Michael Berkowski · Accepted Answer · 2012-06-28 02:52:10Z

Your existing query is returning everything you need to produce the concatenated columns. If you wrap your existing query in a subquery, you can GROUP_CONCAT() both columns and GROUP BY attribute_name:

SELECT attribute_name, GROUP_CONCAT(attribute_value_id) AS attribute_value_ids, GROUP_CONCAT(attribute_value) AS attribute_values FROM ( /* Wrap the body of your existing query in a subselect */ SELECT a.name AS attribute_name, av.attribute_value_id, av.value AS attribute_value FROM attribute_value av INNER JOIN attribute a ON av.attribute_id = a.attribute_id WHERE av.attribute_value_id IN (SELECT attribute_value_id FROM property_attribute WHERE property_id = 1) ) attr_groups GROUP BY attribute_name ORDER BY attribute_name;

That's just awesome @Michael. I learned something new today thanks to you.
If your first GROUP_CONCAT had more values than your second, your second will end up with duplicates. Add DISTINCT keyword inside of GROUP_CONCAT to ensure that you don't get duplicates.

Michael Durrant · Accepted Answer · 2012-06-28 02:54:01Z

2

SELECT group_concat(a.name, av.attribute_value_id, av.value)

answered Jun 28, 2012 at 2:54

Michael Durrant

97.1k102 gold badges352 silver badges536 bronze badges

Comments

lucasarr · Accepted Answer · 2023-12-01 10:32:10Z

Wouldn't a simple:

 SELECT a.name AS attribute_name, av.attribute_value_id, av.value AS attribute_value FROM attribute_value av INNER JOIN attribute a ON av.attribute_id = a.attribute_id WHERE av.attribute_value_id IN (SELECT attribute_value_id FROM property_attribute WHERE property_id = 1) GROUP BY attribute_name ORDER BY a.name;

work?

Collectives™ on Stack Overflow

MySQL: is it possible to group_concat multiple rows?

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related