Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Transposing a table



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

5+ Year Member

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?


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

WebmasterWorld Senior Member 10+ Year Member

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"

Featured Threads

Hot Threads This Week

Hot Threads This Month