Posted on Aug 11, 2022 • Originally published at katiekodes.com on Aug 11, 2022

UNPIVOT to key-value pair in Oracle SQL

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