Is it possible to joining multiple row-CodePudding

I am looking for a way to join two rows with same value (promo code in my case). So for example, I have a table:

ID  PROMO_CODE   PROMO_NAME   DESCRIPTION       LANGUAGE
1   PC123        ABC          Desc in English   ENG
2   PC123        CBA          Desc in Español   ESP

and I want the result like:

ID PROMO_CODE  PROMO_NAME_ENG  PROMO_NAME_ESP  DESCRIPTION_ENG   DESCRIPTION_ESP
1  PC123       ABC             CBA             Desc in English   Desc in Español

Any help will be appreciate

CodePudding user response：

Join on PROMO_CODE:

select
  a.ID,
  a.PROMO_CODE,
  a.PROMO_NAME as PROMO_NAME_ENG,
  b.PROMO_NAME as PROMO_NAME_ESP,
  a.DESCRIPTION as DESCRIPTION_ENG,
  b.DESCRIPTION as DESCRIPTION_ESP
from mytable a
left join mytable b on b.PROMO_CODE = a.PROMO_CODE
  and b.LANGUAGE = 'ESP'
where a.LANGUAGE = 'ENG'

Using a left join guarantees the ENG data even if the ESP data doesn't exist, which seems possible due to ENG's ID being in the output, but not ESP's ID