DEV Community

Katie
Katie

Posted on • Originally published at katiekodes.com on

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

Top comments (0)

Read next

pinky057 profile image

Introduction to PL/SQL

ishrat -

abhirockzz profile image

[20 Days of DynamoDB] Day 14 - Using the DynamoDB expression package to build Key Condition and Filter expressions

Abhishek Gupta -

esproc_spl profile image

How come there are tens of thousands of tables in a database

Judy -

swapnanilwebdeveloper profile image

Mongoose find() Query : operators gt , lt ,gte, lte, in , all, nin # Challenge Your Skills

swapnanilWebDeveloper -