I always forget how to use UNPIVOT, and so many documents are focused on numbers data, not generic key-value data. Here’s the thing I actually often need to do.
Code
Here’s my source table called source_table, inside of an Oracle database:
| record_id | country | gender | name_lf | other_junk |
|---|---|---|---|---|
| 123 | Azerbaijan | F | Amjit, Anush | 123abc |
| 456 | Zimbabwe | N | Borges, Benita | def456 |
Here’s the SQL query:
select
record_id
, field_key -- Arbitrary naming choice; call this anything you want.
, field_value -- Arbitrary naming choice; call this anything you want.
, name_lf
from source_table
unpivot (
field_value -- Arbitrary naming choice; call this anything you want.
for field_key -- Arbitrary naming choice; call this anything you want.
in (
country
, gender
)
);
And here’s the resultant data:
| record_id | field_key | field_value | name_lf |
|---|---|---|---|
| 123 | COUNTRY | Azerbaijan | Amjit, Anush |
| 123 | GENDER | F | Amjit, Anush |
| 456 | COUNTRY | Zimbabwe | Borges, Benita |
| 456 | GENDER | N | Borges, Benita |
Top comments (0)