Welcome to WebmasterWorld Guest from 54.196.208.6

Forum Moderators: open

Message Too Old, No Replies

Transposing a table

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

Junior Member

5+ Year Member

joined:Nov 30, 2008
posts: 42
votes: 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?
11:47 pm on Mar 27, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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"