How to choose FK as the names and values ​​of the headers 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 | [e-mail] | 3 2 | 2 | abc | 3 3 | 1 | [e-mail] | 4 4 | 2 | qwe | 4 

I want to choose:

 Email | City ------------------- [e-mail] | abc [e-mail] | qwe 

How can i do this?

Answer 1

You can try the following:

 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

 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 with PIVOT , but I personally found the syntax above clear and easy to use than PIVOT . If you have dynamic fields, you also need to make this request general. I would suggest that a function is created that analyzes all the individual values ​​in the first table, iterates through them and returns the correct query (you need to add MAX(CASE WHEN field=N THEN value END) AS [Field_N_Name] for each ID in the first table

Answer 3

I do not see how you can do this in a single select statement.

This is a bit confusing, but I think it might 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 first select an email address, and then select a city and finally join them so that they appear in the same row of results.

Answer 4

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

"auto-join". But this is not a general solution that will break when adding field 3.