SQL table table name combination

Question

I have a table structure as follows:

Table: emp_details

identifier name

1 phone [phone]
1 Toronto address
1 age 25
1 sex man
1 name of winner

Table: emp

ID

1
2
3
4
.
.
,

, ​​ : :
emp.id id, emp1.value phone, emp2.value , emp3.value age, emp4.value sex, emp5.value firstname
  emp,
( id, emp_details, name = 'phone') emp1, emp1.id = emp.id,
( id, emp_details, name = 'address') emp2, emp2.id = emp.id,
( id, emp_details, name = 'age') emp3, emp3.id = emp.id,
( id, emp_details, name = 'sex') emp4, emp4.id = emp.id,
( id, emp_details, name = 'firstname') emp5, emp5.id = emp.id

emp.id = 1

?

!

Answer 1

,

    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 

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

, -.

emp_details :

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

, , :

SELECT * FROM emp_details WHERE ID = 1

, , , , 1 (Bob ).

, , . , .

Google.

Answer 3

I believe that this should work, because you know in advance the names of the properties you are interested in. In this example, null values ​​are valid and will return rows that do not have a property that will still allow you to return other fields for this identifier.

DECLARE @emp_details TABLE (
    id int,
    name varchar(50),
    value varchar(50),
    PRIMARY KEY (id, name)
)

DECLARE @emp TABLE (
    id int PRIMARY KEY
)

INSERT INTO @emp_details (
    id,
    name,
    value
)
VALUES
    (1, 'phone', '123-456-8765'),
    (1, 'address', 'Toronto'),
    (1, 'age', '25'),
    (1, 'sex', 'male'),
    (1, 'firstname', 'victor')

INSERT INTO @emp (
    id
)
VALUES
    (1),
    (2),
    (3),
    (4)

SELECT
    emp.id AS id,
    empphone.value AS phone,
    empaddress.value AS address,
    empage.value AS age,
    empsex.value AS sex,
    empfirstname.value AS firstname
FROM
    @emp emp
    LEFT JOIN @emp_details empphone
        ON emp.id = empphone.id
            AND empphone.name = 'phone'
    LEFT JOIN @emp_details empaddress
        ON emp.id = empaddress.id
            AND empaddress.name = 'address'
    LEFT JOIN @emp_details empage
        ON emp.id = empage.id
            AND empage.name = 'age'
    LEFT JOIN @emp_details empsex
        ON emp.id = empsex.id
            AND empsex.name = 'sex'
    LEFT JOIN @emp_details empfirstname
        ON emp.id = empfirstname.id
            AND empfirstname.name = 'firstname'
WHERE
    emp.id = 1