How to select FK as header names and values as a list of these values?

I have the following structure:

TABLE: Field
ID |  Name  
---|--------
 1 |  Email
 2 |  City

And

TABLE: Answers
ID |  Field  | Value        |  User
-----------------------------------
 1 |    1    | m1@mail.com  |   3
 2 |    2    | abc          |   3
 3 |    1    | m2@mail.com  |   4
 4 |    2    | qwe          |   4

I want to select:

开发者_高级运维

Email       | City
-------------------
m1@mail.com | abc
m2@mail.com | qwe

How can I do it?

---

You can try this:

DECLARE @columns NVARCHAR(MAX)

SELECT @columns = COALESCE(@columns + ',[' + cast(f.[Name] as varchar) + ']',
'[' + CAST(f.[Name] as VARCHAR)+ ']')
FROM Answers AS a INNER JOIN Field AS f ON a.[Field] = f.[ID]
GROUP BY f.[Name]

DECLARE @query NVARCHAR(MAX)

SET @query = '
SELECT * FROM
(SELECT f.[Name], a.[Value], a.[User]
FROM Answers AS a INNER JOIN Field AS f ON a.[Field]
= f.[ID]) AS s
PIVOT (MAX(Value) FOR [Name] IN (' + @columns + ')) AS p'

EXECUTE(@query);

---

I don't see how you can do that in a single select statement.

It's a little confusing, but I think this could work:

SELECT
    External.Value as Email, 
    City 
FROM
    Answers as External
JOIN
(
    SELECT
        Answers.Value as City,
        Answers.User
    FROM
        Answers
    WHERE
        Answers.Field = 2 
) AS Internal
ON
(External.User = Internal.User)
WHERE 
    External.Field = 1 

Since the column is the same, I'm first selecting the email and then selecting the city, and finally joining them both so they appear in the same result row.

---

SELECT  User, 
MAX(CASE WHEN field=1 THEN value END) AS [Email],  
MAX(CASE WHEN field=2 THEN value END) as [City]
FROM test
GROUP BY  User;

You can also do the same using PIVOT, but personally I found the syntax above clearer and easier to use than PIVOT. If you have dynamic fields, you need to make this query generic also. I'd assume creating function that analyzes all distinct values in in the first table, iterates through them, and returns a proper query ( You need to append MAX(CASE WHEN field=N THEN value END) AS [Field_N_Name] for each ID in the first table

---

SELECT A1.Value, A2.Value FROM Answers A1 JOIN Answers A2 on A1.User = A2.User

"Self-join". But this is a non-generic solution that will break when you add Field 3.