pivot - MySQL return multiple rows from joined table as columns -

i have 2 tables (shop , opening hours).

i wish return shop opening hours columns rather separate rows. i've seen few ideas on stack none can transpose own situation.

stores ------ id name  opening hours ------------- store_id dayofweek openinghh openingmm closinghh closingmm 

each store have 7 entries in opening hours table (one each day of week)

the returned row be

store_id | name | monday opening | monday closing |tues opening | tues closing etc 

not hard :)

select s.id store_id , s.name store_name ## , concat(oh_mon.openinghh, ':', oh_mon.openingmm) monday_opening , concat(oh_mon.closeinghh, ':', oh_mon.closeingmm) monday_closing ## , concat(oh_tue.openinghh, ':', oh_tue.openingmm) tuesday_opening , concat(oh_tue.closeinghh, ':', oh_tue.closeingmm) tuesday_closing ## , concat(oh_wed.openinghh, ':', oh_wed.openingmm) wednesday_opening , concat(oh_wed.closeinghh, ':', oh_wed.closeingmm) wednesday_closing ## stores s ## left join opening_hours oh_mon on oh_mon.store_id = s.id , oh_mon.dayofweek = 'monday' ## left join opening_hours oh_tue on oh_tue.store_id = s.id , oh_tue.dayofweek = 'tuesday' ## left join opening_hours oh_wed on oh_wed.store_id = s.id , oh_wed.dayofweek = 'wednesday' ## 1=1 

and use left join each day want shown