dynamic sql pivot table

Question

Hi Sirs,

i hope you can help me with the resolution of this task we have.

originally we have these tables:

hwtype
id  name
1   router
2   switch

hwelement
id   idhwtype  name
1    1         RTR1
2    1         RTR2
3    2         SWT1

hwattributes
id  idhwtype name
1   1        speed
2   1        IP
3   2        ports

hwtypeattributes
id  idhwelement    idhwattribute value
1   1              1             100mb
2   1              2             172.16.3.23
3   2              1             10mb
4   2              2             172.16.3.26
5   3              3             8

what we need now is a function that presents the data in this way (according hwtype )

for hwtype.name =router

element   speed  IP
RTR1      100mb  172.16.3.23
RTR2      10mb   172.16.3.26

The idea is to make the tables able to include new element types, elements and attributes without having to modify the tables coding.

I had been looking for examples but unfortunately i had found good ones that do aggregation on values which is something i had not consider.

thanks in advance for your help

Answer 1

You're using the EAV antipattern. This breaks all sorts of rules of relational database design and as you have discovered, getting data out is very awkward. There are many other weaknesses of this design, recounted elsewhere.

Read the article "Bad CaRMa" for a great story of how an EAV system destroyed a company.

Here's what you have to do to get the router attributes out of your database:

SELECT e.name AS "element", 
       speedval.value AS "speed", 
       ipval.value AS "IP",
       portsval.value AS "Ports"
FROM hwtype t 
JOIN hwelement e ON (e.idhwtype = t.id)
JOIN hwattributes speed ON (speed.idhwtype = t.id AND speed.name = 'speed')
LEFT OUTER JOIN hwtypeattributes speedval 
  ON (speedval.idhwattribute = speed.id AND speedval.idhwelement = e.id)
JOIN hwattributes ip ON (ip.idhwtype = t.id AND ip.name = 'ip')
LEFT OUTER JOIN hwtypeattributes ipval 
  ON (ipval.idhwattribute = ip.id AND ipval.idhwelement = e.id)
JOIN hwattributes ports ON (ports.idhwtype = t.id AND ports.name = 'ports')
LEFT OUTER JOIN hwtypeattributes portsval 
  ON (portsval.idhwattribute = ports.id AND portsval.idhwelement = e.id)
WHERE t.name = 'router';

Note that you need an extra pair of joins for each attribute if you insist on fetching all attributes for a given element on a single row. This quickly gets prohibitively expensive for the SQL optimizer.

It's far easier to fetch the attributes on multiple rows, and sort it out in application code:

SELECT e.name AS "element", a.name, v.value
FROM hwtype t 
JOIN hwelement e ON (e.idhwtype = t.id)
JOIN hwattributes a ON (a.idhwtype = t.id)
JOIN hwtypeattributes v ON (v.idhwattribute = a.id AND v.idhwelement = e.id)
WHERE t.name = 'router';