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

Question

I have the following structure:

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

And

TABLE: Answers
ID |  Field  | Value        |  User
-----------------------------------
 1 |    1    | [email protected]  |   3
 2 |    2    | abc          |   3
 3 |    1    | [email protected]  |   4
 4 |    2    | qwe          |   4

I want to select:

Email       | City
-------------------
[email protected] | abc
[email protected] | qwe

How can I do it?

Answer 1

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);

Answer 2

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.