2010-03-26

Pivoting EAV

If you are responsible for designing a data model and just consider to invent again and create this fine generic entity attribute value structure, maybe you should consider attending some teaching about the issue. For example some available soon by C.J. Date in and near Finland.

Well maybe you have a EAV model that you have to deal with. Example


SQL> create table eav as
2 select 1 e, 'first' a, 'Timo' v from dual union all
3 select 1 e, 'last' a, 'Raitalaakso' v from dual union all
4 select 1 e, 'nic' a, 'Rafu' v from dual union all
5 select 2 e, 'first' a, 'John' v from dual union all
6 select 2 e, 'last' a, 'Doe' v from dual
7 ;

Table created.

SQL> select * from eav;

E A V
---------- ----- -----------
1 first Timo
1 last Raitalaakso
1 nic Rafu
2 first John
2 last Doe


You should not query it in a basic case using joins. Most possibly you have tens of joins to the same table.


SQL> select la.e, fi.v firs, la.v las
2 from eav la, eav fi
3 where la.e=fi.e
4 and fi.a='first'
5 and la.a='last'
6 ;

E FIRS LAS
---------- ----------- -----------
1 Timo Raitalaakso
2 John Doe


It is a pivot you want to do.

SQL> select e, firs, las
2 from eav
3 pivot (max(v) for a in ('first' as firs, 'last' as las))
4 ;

E FIRS LAS
---------- ----------- -----------
1 Timo Raitalaakso
2 John Doe


With the pivot you get the nullable columns also easier without filtering out the whole entity

SQL> select e, firs, las, ni
2 from eav
3 pivot (max(v) for a in ('first' as firs, 'last' as las, 'nic' as ni))
4 ;

E FIRS LAS NI
---------- ----------- ----------- -----------
1 Timo Raitalaakso Rafu
2 John Doe


Maybe you do not have 11g features available.

SQL> select e
2 , max(case when a = 'first' then v end) firs
3 , max(case when a = 'last' then v end) las
4 from eav
5 group by e
6 ;

E FIRS LAS
---------- ----------- -----------
1 Timo Raitalaakso
2 John Doe


And the best thing to do with it might be.

SQL> drop table eav purge;

Table dropped.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.