0

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?

Kellerness
  • 63
  • 5
  • Does this answer your question? [How to convert Rows to Columns in Oracle?](https://stackoverflow.com/questions/19858079/how-to-convert-rows-to-columns-in-oracle) – astentx Jul 07 '21 at 09:12

1 Answers1

2

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

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks a bunch! Which one of the two would be fastest to implement? It's going into a view with many rows and joins & dependencies on other tables – Kellerness Jul 07 '21 at 10:48
  • Pivot is converted to aggregation in the background anyway, so I doubt there would be much difference. Pivot is maybe easier to maintain if you have a lot of values, or need to add values, but I'd use whichever makes most sense to you. – Alex Poole Jul 07 '21 at 11:08