CODEWITHSUNDEEP
sqloracle-database
Kellerness
Kellerness

Reputation: 69

Getting two columns out of a single column with case statement

I have a table with languages of persons. Any person can either speak English, Spanish or both English and Spanish. In the first two cases I have one row per person_id, in the second I have two rows.

I need to use this data in another table but I can only retain one unique row per person. The correct solution in my case is to create two columns:

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;

This gets the right data into the right column, but the English & Spanish are each shown on two separate rows having a NULL value in the other language column. Any idea on how to solve this?

Upvotes: 0

Views: 1662

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can use aggregation:

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;

This could also be done with the pivot syntax:

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;

but it's the same mechanism underneath.

db<>fiddle

Upvotes: 2

Related Questions