sql server - SQL PIVOT SELECT FROM LIST (IN SELECT)

Question

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: I do NOT want to use dynamic SQL, is there a way to do this without using dynamic SQL?

Answer 1

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.