By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_1 ( id, key, value ) as
select 1,'phone_num_1','111-111-1111' from dual union all
select 1,'phone_num_2','222-222-2222' from dual union all
select 1,'contact_1','friend' from dual union all
select 1,'contact_2','family' from dual union all
select 1,'first_name','mike' from dual union all
select 1,'last_name','smith' from dual union all
select 2,'phone_num_1','333-333-3333' from dual union all
select 2,'phone_num_2','444-444-4444' from dual union all
select 2,'contact_1','family' from dual union all
select 2,'contact_2','friend' from dual union all
select 2,'first_name','john' from dual union all
select 2,'last_name','adams' from dual union all
select 3,'phone_num_1','555-555-5555' from dual union all
select 3,'phone_num_2','666-666-6666' from dual union all
select 3,'phone_num_3','777-777-7777' from dual union all
select 3,'contact_1','work' from dual union all
select 3,'contact_2','family' from dual union all
select 3,'contact_3','friend' from dual union all
select 3,'first_name','mona' from dual union all
select 3,'last_name','lisa' from dual
20 rows affected
SELECT *
FROM (
SELECT id,
CASE
WHEN key LIKE 'phone_num_%' THEN 'phone_num'
WHEN key LIKE 'contact_%' THEN 'contact'
ELSE key
END AS key,
CASE
WHEN key LIKE 'phone_num_%'
OR key LIKE 'contact_%'
THEN TO_NUMBER( SUBSTR( key, INSTR( key, '_', -1 ) + 1 ) )
ELSE NULL
END AS item,
value,
MAX( CASE key WHEN 'first_name' THEN value END )
OVER ( PARTITION BY id ) AS first_name,
MAX( CASE key WHEN 'last_name' THEN value END )
OVER ( PARTITION BY id ) AS last_name
FROM table_1
)
PIVOT( MAX( value ) FOR key IN ( 'contact' AS contact, 'phone_num' AS phone_num ) )
WHERE item IS NOT NULL
ORDER BY id, item
| ID | ITEM | FIRST_NAME | LAST_NAME | CONTACT | PHONE_NUM |
|---|---|---|---|---|---|
| 1 | 1 | mike | smith | friend | 111-111-1111 |
| 1 | 2 | mike | smith | family | 222-222-2222 |
| 2 | 1 | john | adams | family | 333-333-3333 |
| 2 | 2 | john | adams | friend | 444-444-4444 |
| 3 | 1 | mona | lisa | work | 555-555-5555 |
| 3 | 2 | mona | lisa | family | 666-666-6666 |
| 3 | 3 | mona | lisa | friend | 777-777-7777 |