UNPIVOT to key-value pair in Oracle SQL

Katie - Aug 11 '22 - - Dev Community

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

Enter fullscreen mode Exit fullscreen mode

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