Need help with SQL Query

Question

Let's say I have 2 tables:

Person

 - Id
 - Name

PersonAttribute

 - Id
 - PersonId
 - Name
 - Value

Next, let's say that each person had 2 attributes (say, gender and age). An example entry would be:

Person->Id = 1
Person->Name = 'John Doe'

PersonAttribute->Id = 1
PersonAttribute->PersonId = 1
PersonAttribute->Name = 'Gender'
PersonAttribute->Value = 'Male'

PersonAttribute->Id = 2
PersonAttribute->PersonId = 1
PersonAttribute->Name = 'Age'
PersonAttribute->Value = '30'

Question: how can I request this so that I get this result:

'John Doe', 'Male', '30'

Answer 1

SELECT p.name, p1.Value, p2.Value 
     FROM Person p, PersonAttribute p1, PersonAttribute p2 
     WHERE p.Id = p1.PersonId AND p.Id = p2.PersonId 
        AND p1.Name = 'Gender' AND p2.Name = 'Age'

Answer 2

I think you need to redesign your circuit. Why not?

Person

 - Id
 - Name
 - Gender
 - Birthday
...

Answer 3

SELECT p.Name, g.Value, a.Value
FROM Person p INNER JOIN PersonAttribute g ON p.Id = g.Id g.Name = ""
INNER JOIN PersonAttribute a ON p.Id = a.Id AND a.Name =" Age"

Answer 4

, . http://www.simple-talk.com/community/blogs/philfactor/archive/2008/05/29/56525.aspx

Answer 5

, PIVOT , , .

Answer 6

.

( ) , , , , , . , , !

X , 20-30, , . .

select piv.name, 
    max(case piv.a_name when 'Gender' then piv.a_value else null end) as Gender,
    max(case piv.a_name when 'Age' then piv.a_value else null end) as Age,
    max(case piv.a_name when 'Hobby' then piv.a_value else null end) as Hobby
from 
(select p.name as name, pa.name as a_name, pa.value as a_value 
from person p, personattribute pa
where p.id = pa.personid) piv
group by piv.name

:

   name    | gender | age |  hobby  
-----------+--------+-----+---------
 Bob Swift | Male   |     | Reading
 John Doe  | Male   | 30  | 
(2 rows)

, . .

NAME , VALUE.

Entity-Attribute , .

Answer 7

You need to SAVE two tables. Wikipedia gives a pretty good explanation of JOIN: http://en.wikipedia.org/wiki/Join_%28SQL%29

Answer 8

SELECT Name, g.Value, a.Value
FROM Person, 
PersonAttribute g INNER JOIN ON g.Name = "Gender", 
PersonAttribute a INNER JOIN ON a.Name = "Age"