mysql how do i convert row results to column with new column name?

Question

I have 2 mysql tables that i want to join, but i want the column 'category' results to be in a new column, not rows.

the first one looks something like this

table name = petshop.product
|==========================|
|product_id | product_name |
|==========================|
|     1     | Dog & Cat Toy|
|     2     | Dog Food     |
|==========================|

and the second one looks like this

table name = petshop.category
|============================|
|category_id | category_name |
|============================| 
|      1     |Dog            |
|      2     |Food           |
|      3     |Toy            |
|      4     |Cat            | 
|============================|        

and i have another table to store the relations that looks like this

table name = petshop.product_category
=================================
|pc_id| product_id | category_id|
|===============================|
|  1  |     1      |      1     |
|  2  |     1      |      3     |
|  3  |     1      |      4     |
|  4  |     2      |      1     |
|  5  |     2      |      2     |
|===============================|

how can i output a table where the result looks like this

|====================================================|
|product_name|  category1  |  category2 |  category3 |
|====================================================|
|Dog&Cat Toy |  Dog        | Toy        | Cat        |
|Dog Food    |  Dog        | Food       | NULL       |
|====================================================|

I read about pivot but i can't wrap my head around it (really new to mysql and coding in general to be honest). Thank you!

Answer 1

In MySQL 8.0, you can address this with joins, row_number() to enumerate the categories per product, and conditional aggregation to pivot the resultset:

select 
    product_name,
    max(case when rn = 1 then category_name end) category1,
    max(case when rn = 2 then category_name end) category2,
    max(case when rn = 3 then category_name end) category3
from (
    select 
        p.product_id,
        p.product_name,
        c.category_name,
        row_number() over(partition by p.product_id order by c.category_id) rn
    from product p
    inner join product_category pc on pc.product_id = p.product_id
    inner join category c on c.category_id = pc.category_id
) t
group by product_id, product_name