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

10

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;

answered Jun 28, 2012 at 2:52

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brandon Minton Over a year ago

That's just awesome @Michael. I learned something new today thanks to you.

Buttle Butkus Over a year ago

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.2k102 gold badges353 silver badges536 bronze badges

Comments

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

0

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?

answered Dec 1, 2023 at 10:32

lucasarr

514 bronze badges

Collectives™ on Stack Overflow

MySQL: is it possible to group_concat multiple rows?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related