SQL PIVOT SELECT FROM LIST (IN SELECT)

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?

Answers


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.


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)

Need Your Help

Using as.Date in R to convert to dates and getting series of "NA"s

r date formatting import-from-csv

Using the following code to try to get my date column into the right format, but it's just returning all of the dates as "NA".

Abstracted References Between Entities

database-design architecture business-objects

An upcoming project of mine is considering a design that involves (what I'm calling) "abstract entity references". It's quite a departure from a more common data model design, but it may be necessa...