Name: UNPIVOT to key-value pair in Oracle SQL
Rating: 1.3 (3065 reviews)
Author: katiekodes

UNPIVOT to key-value pair in Oracle SQL

Katie - Aug 11 '22 -

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