Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table person_table (person_id, person_language) as
select 1, 'English' from dual
union all
select 2, 'Spanish' from dual
union all
select 3, 'English' from dual
union all
select 3, 'Spanish' from dual
union all
select 3, 'French' from dual;
5 rows affected

Select
 person_id,
 case when person_language = 'English' then person_language end as "english_language",
 case when person_language = 'Spanish' then person_language end as "spanish_language"
from person_table;


PERSON_ID
english_language
spanish_language


1
English
null

2
null
Spanish

3
English
null

3
null
Spanish

3
null
null


Select
 person_id,
 max(case when person_language = 'English' then person_language end) as "english_language",
 max(case when person_language = 'Spanish' then person_language end) as "spanish_language"
from person_table
group by person_id
order by person_id;


PERSON_ID
english_language
spanish_language


1
English
null

2
null
Spanish

3
English
Spanish


select person_id, english_language, spanish_language
from person_table
pivot (
  max(person_language) as language
  for (person_language) in ('English' as english, 'Spanish' as spanish)
)
order by person_id;


PERSON_ID
ENGLISH_LANGUAGE
SPANISH_LANGUAGE


1
English
null

2
null
Spanish

3
English
Spanish

PERSON_ID	english_language	spanish_language
1	English	null
2	null	Spanish
3	English	null
3	null	Spanish
3	null	null