Transposing a table

Forum Moderators: open

Message Too Old, No Replies

Transposing a table

fm86

3:17 pm on Mar 27, 2012 (gmt 0)

Good evening!

I have this table:

table(id, attrName, value)

so for example:

(1, "name", "anna")
(1, "surname", "bla")
(1, "age", 21)
(2, "name", "Rufus T.")
(2, "surname", "Firefly")
(2, "age", 89)
(3, "name", "marco")
(3, "surname", "hahn")
(3, "age", 30)

I know it is an ugly non-optimized table, but that's what I have :)
I guess there should be a way to obtain from a select query something like

(id, name, surname, age)

1, Anna, Bla, 21
2, Rufus T., Firefly, 89
3, Marco, Hahn, 30

Transposing is probably the solution but I have no idea how to implement it. Can someone help me, please?

Dijkgraaf

11:47 pm on Mar 27, 2012 (gmt 0)

I haven't tested this, but you would need to join the three tables together and alias them.
This however only works if values exist on all three tables for a particular ID, if this isn't the case then it becomes a bit trickier and you have to use full outer joins.

SELECT firstname.id, firstname.value as name, lastname.value as surname, years.value as age
FROM table as firstname
INNER JOIN table as lastname ON lastname.id = firstname.id AND firstname.attrName = "name" AND lastname.attrName = "surname"
INNER JOIN table as years ON years.id = firstname.id AND years.attrName = "age"