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?

Solution 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.

Solution 2

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)

Comments

[rvphx]

Thats what the point is. CoderGal doesnt want to use dynamic SQL.

[Jez]

Why doesn't the parser know the values up front? It could complete the SELECT query first.

[Jay]

It may not help the poster, but it helped to remind me that its possible! so +1

[QHarr]

Where does rm come from? Some sample data along would have helped this example.

[mo_maat]

Great solution. Thanks. I think that second line should be a SELECT, not a SET.