Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

UNPIVOT to key-value pair in Oracle SQL

11 Aug 2022 🔖 databases sql
💬 EN

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.

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