homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

Transposing a table

 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?



 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"

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved