A complex SQL Query using group_concat
===

I have a table called 'stock':


    stock_id | prod_id | size | color | shop_id | qty
    
    1        |   4     |   L  |  Red  |    1    |  3
    2        |   4     |   XL |  Blue |    2    |  1
    3        |   4     |   L  |  Red  |    3    |  2
    4        |   2     |  XXL | White |    1    |  7
    5        |   4     |   L  |  Red  |    4    |  1

Some of that..

I have a products table and a shop table.

What I want as result is something like that:

    prod_id | prod_name | shop_name |  color_size_qty
       4    |  T-Shirt  |  Shop 1   |    Red,L,(3)
       2    |  T-Shirt  |  Shop 1   |    White,XXL,(7)
       4    |  T-Shirt  |  Shop 2   |    Blue,XL,(1)

I dont know if you can understand me. The closest I got was something like

    prod id | prod_name |        Colors         |     sizes   |
       4    | T-Shirt   |Red,Red,Red,Blue,White | L,L,L,XL,XL  

Not even close as you can see. Hope you can help me!

Thank you.