sql - 如何选择 FK 作为 header 名称和值作为这些值的列表？

Question

我有以下结构:

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

还有

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

我要选择:

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

我该怎么做？

Answer 1

你可以试试这个:

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