homepage Welcome to WebmasterWorld Guest from 54.227.171.163
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Transposing a table
fm86

5+ Year Member



 
Msg#: 4433935 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4433935 posted 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.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved