Is it possible to GROUP_CONCAT a result set as a new column?

Question

I have one result set in the format:

category name  description author
ABC      Lorem Ipsum       Dolor
DEF      Foo   Bar         Baz

I have a second result set in the format:

category product
ABC      Plane
DEF      Car
ABC      Boat
GHI      Truck

I would like the end result to look like the following:

category name  description author productlist
ABC      Lorem Ipsum       Dolor  "Plane, Boat"
DEF      Foo   Bar         Baz    "Car"

I have been trying to use GROUP_CONCAT on the second result set and joining it to the first result set by category to get the column productlist, but have not had success in creating the right query.

Is something like this possible?

I am using MariaDB.

Performance is not a major concern yet as the result sets tend to be small.

This is the closest I have gotten,

SELECT `NAME`, `description`, `author`, `category`, ProductList
FROM (long query) q
LEFT JOIN (
    SELECT GROUP_CONCAT(b.product ORDER BY b.product SEPARATOR ', ') AS 
        ProductList, ObjectCategory FROM SOME_TABLE a, SOME_OTHER_TABLE b 
    WHERE ObjectCategory = 'ABC' ORDER BY product
) q2
ON q.category = q2.ObjectCategory

but the above only works for one hard-coded category at a time ("ABC" in the above case).

score 2 · Accepted Answer · answered Jan 06 '20 at 20:25

Yes, quite possible. Aggregate before joining:

select r1.*, r2.product_list
from results1 r1 left join
     (select r2.category, group_concat(product) as product_list
      from results2 r2
      group by r2.category
     ) r2
     on r2.category = r1.category;

Is it possible to GROUP_CONCAT a result set as a new column?

1 Answers1