Oracle: How can I pivot an EAV table with a dynamic cardinality for certain keys?

Question

I have the following Entity–attribute–value (EAV) table in Oracle:

| ID |     Key     |    Value     |
|----|-------------|--------------|
|  1 | phone_num_1 | 111-111-1111 |
|  1 | phone_num_2 | 222-222-2222 |
|  1 | contact_1   | friend       |
|  1 | contact_2   | family       |
|  1 | first_name  | mike         |
|  1 | last_name   | smith        |
|  2 | phone_num_1 | 333-333-3333 |
|  2 | phone_num_2 | 444-444-4444 |
|  2 | contact_1   | family       |
|  2 | contact_2   | friend       |
|  2 | first_name  | john         |
|  2 | last_name   | adams        |
|  3 | phone_num_1 | 555-555-5555 |
|  3 | phone_num_2 | 666-666-6666 |
|  3 | phone_num_3 | 777-777-7777 |
|  3 | contact_1   | work         |
|  3 | contact_2   | family       |
|  3 | contact_3   | friend       |
|  3 | first_name  | mona         |
|  3 | last_name   | lisa         |

Notice that some keys are indexed and therefore have an association with other indexed keys. For example, phone_num_1 is to be associated with contact_1.

Note: There is no hard limit to the number of indexes. There can be 10, 20, or even 50 phone_num_*, but it's guaranteed that for each phone_num_N, there is a corresponding contact_N

This is my desired result:

| ID |  Phone_Num   | Contact | First_Name | Last_Name |
|----|--------------|---------|------------|-----------|
|  1 | 111-111-1111 | friend  | mike       | smith     |
|  1 | 222-222-2222 | family  | mike       | smith     |
|  2 | 333-333-3333 | family  | john       | adams     |
|  2 | 444-444-4444 | friend  | john       | adams     |
|  3 | 555-555-5555 | work    | mona       | lisa      |
|  3 | 666-666-6666 | family  | mona       | lisa      |
|  3 | 777-777-7777 | friend  | mona       | lisa      |

What have I tried/looked at:

I have looked into the pivot function of Oracle; however, I don't believe that can solve my problem since I don't have a fixed number of attributes that I want to pivot on. I've looked at these posts: SQL Query to return multiple key value pairs from a single table in one row

Pivot rows to columns without aggregate

Question:

Is what I'm tying to accomplish at all possible purely with SQL? If so, how can it be done? If not, please explain why.

Any help is much appreciated and here's the with table to help you get started:

with
    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
     )
select * from table_1;

Answer 1

This is ugly, but I think does what you need

select t1.* , t2.value, t3.n, t3.f
from table_1 t1
inner join table_1 t2 on t1.id = t2.id and REPLACE(t1.key, 'phone_num_', '') = REPLACE(t2.key, 'contact_', '')
inner join (
    select ID, min(case when Key = 'first_name' then Value end) as n, min(case when Key = 'last_name' then Value end) as f
    from table_1
    group by ID
) t3 on t1.id = t3.id
where
t1.Key not in('first_name','last_name')

Answer 2

This is not a dynamic pivot as you have a fixed set of keys - you just need to separate the enumeration of the keys from the keys themselves first.

You need to:

• Separate the phone_num and contact key prefixes from the enumerated item; then
• Pivot the common keys that have no enumeration so that they are associated with each enumerated key; and finally,
• Pivot a second time to get the enumerated keys in a row together.

Oracle Setup:

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

Query:

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

Output:

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

db<>fiddle here

---

If you can refactor the table then a simple improvement would be to add an extra column to hold the enumeration of the keys and use NULL when it is a value common to every enumeration:

CREATE TABLE table_1 ( id, key, line, 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', NULL, 'mike'         from dual union all
select 1, 'last_name',  NULL, 'smith'        from dual

Then your set of keys is always fixed and you do not need to extract the enumeration value from the key.

Answer 3

SELECT id,
phone,
contact, first_value(last) IGNORE NULLS over (partition BY id order by id DESC range BETWEEN CURRENT row AND unbounded following ) last_name,
first_value(FIRST) IGNORE NULLS over (partition BY id order by id DESC range BETWEEN CURRENT row AND unbounded following ) first_name
FROM (SELECT id,
value,
row_number() over ( partition BY id,SUBSTR(KEY,1 ,instr(KEY,'',1)-1) order by KEY) rn, SUBSTR(KEY,1 ,instr(KEY,'',1) -1) KEY FROM table_1
) pivot ( MAX(value) FOR KEY IN ( 'phone' AS phone,'last' AS last,'first' AS FIRST,'contact' AS contact)) ORDER BY id;