SQL PIVOT 从列表中选择（在选择中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6060526/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL PIVOT SELECT FROM LIST (IN SELECT)
提问by live-love
Is it possible to do a PIVOT and select list from a table, instead of using single values?
是否可以执行 PIVOT 并从表中选择列表，而不是使用单个值？
Like this (incorrect syntax error):
像这样（不正确的语法错误）：
SELECT *
FROM (
SELECT RepID, MilestoneID, ResultID FROM RM
) AS src
PIVOT (
MAX(ResultID) FOR MilestoneID IN (SELECT id FROM m)
) AS pvt
This one compiles, but doesn't work for me:
这个可以编译，但对我不起作用：
SELECT *
FROM (
SELECT RepID, MilestoneID, ResultID FROM RM
) AS src
PIVOT (
MAX(ResultID) FOR MilestoneID IN ([1], [2], [3], [4])
) AS pvt
PS: I do NOT want to use dynamic SQL, is there a way to do this without using dynamic SQL?
PS：我不想使用动态 SQL，有没有办法在不使用动态 SQL 的情况下做到这一点？
采纳答案by Yuck
If dynamic SQL is out then I'm afraid the answer is no, it can't be done. The parser needs to know the values up front to perform the pivot to columns.
如果动态 SQL 出来了，那么恐怕答案是否定的，它无法完成。解析器需要预先知道值才能执行对列的透视。
回答by Krunal Savani
It Can be done.
可以办到。
DECLARE @idList varchar(500)
SET @idList = COALESCE(@idList + ',', '') + id
FROM m
DECLARE @sqlToRun varchar(1000)
SET @sqlToRun = '
SELECT *
FROM (
SELECT RepID, MilestoneID, ResultID FROM RM
) AS src
PIVOT (
MAX(ResultID) FOR MilestoneID IN ('+ @idList +')
) AS pvt'
EXEC (@sqlToRun)
