Dynamic Pivot显示选民的投票方式

Question

这是表结构：

Election
ElectionId ElectionName
1          12P
2          11G
History
ElectionId VoterId HowVoted
1          1       Rep
2          2       Dem
1          2       Non 
Voter
VoterId VoterName
1       Bill Smith
2       John Hearst

我想要这样的输出：

VoterName   12P  11P  note: Election names change so need to be pivoted dynamically
Bill Smith  Rep  Null
John Hearst Non  Dem    

我基本上需要将ElectionNames转到Column Names，然后显示选民是如何在一行中投票的。建议？谢谢

Answer 1

您可以使用动态SQL这样做：

-- Get column names
DECLARE @Columns VARCHAR(MAX);  SET @Columns = '';
SELECT @Columns = @Columns + ', [' + ElectionName + ']' FROM Election;
SET @Columns = SUBSTRING(@Columns, 3, LEN(@Columns)) -- Remove the leading ', '
-- Declare dynamic SQL
DECLARE @sql VARCHAR(MAX);
SET @sql = 'WITH CTE AS
(
    SELECT VoterName, ElectionName, HowVoted
    FROM Election e
        INNER JOIN History h ON e.ElectionId = h.ElectionId
        INNER JOIN Voter v ON v.VoterId = h.VoterId
)
SELECT [VoterName], ' + @Columns + 'FROM CTE
PIVOT (MAX(HowVoted) FOR ElectionName IN ('+ @Columns + ')) AS p'
-- Run dynamic SQL
EXEC(@sql)