Combine multiple rows into a column based on a condition

Question

I have two tables as follows.

Category table
+------------+------------+--------------+
| CategoryID | Name       | CategoryCode |
+------------+------------+--------------+
|          1 | Fixed      |              |
|          2 | Consumable |              |
|          3 | Intangible |              |
+------------+------------+--------------+

Type table
+--------+------------+-------------------------+----------+
| TypeID | CategoryID | Name                    | TypeCode |
+--------+------------+-------------------------+----------+
|      1 |          1 | Furniture               |          |
|      2 |          1 | Computers & Peripherals |          |
|      3 |          2 | Keyboards               |          |
|      4 |          2 | Other                   |          |
|      5 |          3 | Software                |          |
+--------+------------+-------------------------+----------+

The result I want is like this

+------------+------------+------------------------------------+
| CategoryID | Category   | Types                              |
+------------+------------+------------------------------------+
|          1 | Fixed      | Furniture, Computers & Peripherals |
|          2 | Consumable | Keyboards, other                   |
|          3 | Intangible | Software                           |
+------------+------------+------------------------------------+

Appriciate if you could help me with wirting the query in MySQL

Read up on the basics of JOINS, if you are unfamiliar - and then go check out GROUP_CONCAT. — C3roe
– C3roe, Commented Sep 28, 2021 at 8:44

Plantour · Accepted Answer · 2021-09-28 08:53:03Z

2

You can try this solution

SELECT
    a.CategoryId,
    a.`Name` Category,
    GROUP_CONCAT(b.`Name`) Types
FROM
    Category a
INNER JOIN Type b ON b.CategoryID = a.CategoryId 
GROUP BY a.CategoryId

answered Sep 28, 2021 at 8:53

Plantour

1065 bronze badges

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

3 Comments

jarlh Over a year ago

I'd also put a.Name in the GROUP BY, to ensure MySQL forward compatibility.

Ergest Basha Over a year ago

@Plantour this will not work with sql_mode=only_full_group_by in order to work. You need all the columns in the select part of the group by or to be an aggregation function like MAX, SUM

Ergest Basha Over a year ago

@Plantour that is what @jarlh meant on the comment, your group by should be : group by a.CategoryId ,a.Name; Demo: db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/89

Collectives™ on Stack Overflow

Combine multiple rows into a column based on a condition

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related