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;
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:
phone_num and contact key prefixes from the enumerated item; thenOracle 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.