database - I have Quantity of item sold of a Brand and its competitor stored in the same column but want it in seperate column

Question

I have a table given in the following format:

Store_Id	Category	Quantity
1	Brand_A	3
1	Brand_A_Competitor	4
1	Brand_B	5
1	Brand_B_Competitor	6
2	Brand_A	8
2	Brand_A_Competitor	4
2	Brand_B	7
2	Brand_B_Competitor	9

question from:https://stackoverflow.com/questions/65640866/i-have-quantity-of-item-sold-of-a-brand-and-its-competitor-stored-in-the-same-co

Answer 1

Join the table to itself, with one side of the join for brand and the other side for the competitor.

select
  a.Store_Id,
  a.Category,
  b.Category Comp_Brand,
  a.Quantity,
  b.Quantity Competitor
from mytable a
left join mytable b on b.Store_Id = a.Store_Id
  and b.Category = a.Category || '_Competitor'
where a.Category not like '%_Competitor'
order by a.Store_Id, a.Category