SQL table joins-name value pairs

Question

Hello, I am having a table structure like this:

Table : emp_details

id name value

1 phone 123-456-8765
1 address Toronto
1 age 25
1 sex male
1 firstname victor

Table:emp

id

1
2
3
4
.
.
.

I want the name value pair to be presented in a flat way: I am doing it in this naive implementation:
select emp.id as id,emp1.value as phone,emp2.value as address,emp3.value as age,emp4.value as sex,emp5.value as firstname
from emp,
(select id, value from emp_details where name = 'phone') emp1 where emp1.id = emp.id,
(select id, value from emp_details where name = 'address') emp2 where emp2.id = emp.id,
(select id, value from emp_details where name = 'age') emp3 where emp3.id = emp.id,
(select id, value from emp_details where name = 'sex') emp4 where emp4.id = emp.id,
(select id, value from emp_details where name = 'firstname') emp5 where emp5.id = emp.id

and emp.id = 1

Could you please suggest a better way to do this?

Thank you very much!

Answer 1

I believe you are looking for table rotation

    SELECT CASE [d.name]
             WHEN 'phone' THEN [d.value]
             ELSE ''
           END AS phone,
           CASE [d.name]
             WHEN 'address' THEN [d.value]
             ELSE ''
           END AS address,
           CASE [d.name]
             WHEN 'age' THEN [d.value]
             ELSE ''
           END AS age,
           CASE [d.name]
             WHEN 'sex' THEN [d.value]
             ELSE ''
           END AS sex,
           CASE [d.name]
             WHEN 'firstname' THEN [d.value]
             ELSE ''
           END AS firstname
    FROM   emp_details d
           INNER JOIN emp e
             ON d.id = e.id
    WHERE  e.id = 1 

And here is anothere way

SELECT phone = Isnull((SELECT [value]
                       FROM   emp_details
                       WHERE  [name] = 'phone'
                              AND [id] = d.[id]),''),
       address = Isnull((SELECT [value]
                         FROM   emp_details
                         WHERE  [name] = 'address'
                                AND [id] = d.[id]),''),
       age = Isnull((SELECT [value]
                     FROM   emp_details
                     WHERE  [name] = 'age'
                            AND [id] = d.[id]),''),
       sex = Isnull((SELECT [value]
                     FROM   emp_details
                     WHERE  [name] = 'sex'
                            AND [id] = d.[id]),''),
       firstname = Isnull((SELECT [value]
                           FROM   emp_details
                           WHERE  [name] = 'firstname'
                                  AND [id] = d.[id]),'')
FROM   emp_details d
       INNER JOIN emp e
         ON d.id = e.id
WHERE  e.id = 1 

Answer 2

The first table would be much easier to use if it were set up differently.

Your table for emp_details could be arranged like this:

ID   phone         address     age     sex   firstname
1    123-123-1234  1 fake st.  12      M     Bob
2    222-222-2222  2 real st.  33      F     Alice

Then when you select a row, you'd get all the data you want:

SELECT * FROM emp_details WHERE ID = 1

This would give you a record with ID, phone, address, age, sex, and firstname of employee with ID 1 (Bob in this example).

The way your table is currently set up will only mean a huge amount of trouble for you in the future. You should fix it now if you still can.

Look into database normalization on Google.