UNPIVOT to key-value pair in Oracle SQL

#database #sql #oracle

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
    )
);