Sql To Group Table Of Product Owners With A Column For Primary Owner And Concatenated Secondary Owners

I have a table of products with their owners. Each owner is in their own row and has an owner type of either primary or secondary. Not every product has a secondary owner. I need t

Solution 1:

One way to do this is assigning row numbers prioritizing owner_type='Primary' rows. Then get the first row as the primary owner and group_concat others to be secondary owners.

selectproduct
,max(case when rnum=1 then owner end) asprimary_owner
,group_concat(case when rnum<>1 then owner end order by rnum) assecondary_ownersfrom (select product,owner_type,owner,
      @rn:=case when @prev_product=product then @rn+1 else 1 end as rnum,
      @prev_product:=product 
      from tablename 
      cross join (select @rn:=0,@prev_product:='',@prev) r
      order by product,owner_type='Primary',owner
     ) tgroupbyproductorderby1

Sample Demo