Join/Pivot Items with Eav Table

Join/Pivot items with EAV table

You should be able to use an aggregate function with a CASE expression to convert the rows into columns:

select d.id,
  d.name,
  max(case when a.attr_name = 'color' then a.value end) color,
  max(case when a.attr_name = 'brand' then a.value end) brand,
  max(case when a.attr_name = 'size' then a.value end) size
from product_description d
inner join product_attributes a
  on d.id = a.id
group by d.id, d.name;

See SQL Fiddle with Demo.

Since you are using Oracle 11g, then you can use the PIVOT function to get the result:

select id, name, Color, Brand, "Size"
from
(
  select d.id, d.name,
    a.attr_name, a.value
  from product_description d
  inner join product_attributes a
    on d.id = a.id
) src
pivot
(
  max(value)
  for attr_name in ('color' as Color,
                    'brand' as Brand,
                    'size' as "Size")
) p;

See SQL Fiddle with Demo

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

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.

PIVOT and INNER JOIN on dynamically created key/value pairs

Perhaps this will help. Notice the inclusion of ITEM and max(Value)

Example or dbFiddle

DECLARE 
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(pf.Name) 
            FROM ProductFields pf
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT * 
                from (
                       SELECT p.ProductId
                             ,p.Name
                             ,Item=pf.Name
                             ,pf.Value 
                         FROM Products p
                         JOIN ProductFields pf
                           ON pf.ProductId = p.ProductId
                     ) x
            pivot 
            (
                max(Value)
                for Item in (' + @cols + ')
            ) pi '

execute(@query)

Join two child records next to each other in one row in PL Sql

I would add a row number to the second table, so that you can join that table once for when that row number is 1, and a second time for when it is 2:

with ext as (
    select Srn_Tablea2.*, 
           row_number() over (partition by id order by addressid) rn
    from   Srn_Tablea2
)
select    a.id, b.address, b.addressid, c.address, c.addressid
from      Srn_Tablea1 a
left join ext b on a.id = b.id and b.rn = 1
left join ext c on a.id = c.id and c.rn = 2;

Of course, if you have cases where you have 3 or more addresses for the same id, you'll have to create more joins, and produce more columns. But the principle remains the same.

Filtering EAV table with multiple conditions

After couple hours of combining and trying, I finally did:

    SELECT * FROM objects as o

/* filter1 join */
INNER JOIN 
    attributes AS f1 
        ON 
    o.object_id = f1.attr_object_id 
        AND 
    f1.attr_property_id = 1
        AND
    f1.attr_value <= '100000'

/* filter2 join */
INNER JOIN 
    attributes AS f2 
        ON 
    f1.attr_object_id = f2.attr_object_id 
        AND 
    f2.attr_property_id = 2
        AND
    f2.attr_value > '2000'

WHERE 
    o.object_group_id = 1

I was too close, and done this by moving all filter conditions to INNER JOIN.

---

Related Topics