SQL PIVOT 从列表中选择(在选择中)

2022-01-22 00:00:00 pivot sql tsql sql-server

是否可以执行 PIVOT 并从表中选择列表，而不是使用单个值?

Is it possible to do a PIVOT and select list from a table, instead of using single values?

像这样(不正确的语法错误):

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:我不想使用动态 SQL，有没有办法在不使用动态 SQL 的情况下做到这一点?

PS: I do NOT want to use dynamic SQL, is there a way to do this without using dynamic SQL?

推荐答案

如果动态SQL出来了，恐怕答案是否定的，做不到.解析器需要预先知道值才能执行对列的透视.

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.

相关文章