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?

Nope, unfortunately you need to specific the values in the `IN` clause. You could use dynamic SQL to inspect the values and then execute the resulting statement. — Yuck, May 19 '11 at 14:55

score 15 · Accepted Answer · answered May 19 '11 at 15:04

15

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.

answered May 19 '11 at 15:04

Yuck

49,664
13
105
135

8

Why doesn't the parser know the values up front? It could complete the `SELECT` query first. – Jez Dec 09 '16 at 19:05

score 10 · Answer 2 · edited Dec 08 '22 at 00:31

10

It Can be done.

DECLARE @idList varchar(500)
SELECT @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)

edited Dec 08 '22 at 00:31

Joundill

6,828
12
36
50

answered Jun 11 '12 at 16:22

Krunal Savani

129
1
2

2

Thats what the point is. CoderGal doesnt want to use dynamic SQL. – rvphx Jun 11 '12 at 16:39
5

It may not help the poster, but it helped to remind me that its possible! so +1 – Jay Jan 09 '17 at 14:10
Where does rm come from? Some sample data along would have helped this example. – QHarr Apr 27 '20 at 08:18
1

Great solution. Thanks. I think that second line should be a `SELECT`, not a `SET`. – mo_maat Nov 11 '20 at 15:54

SQL PIVOT SELECT FROM LIST (IN SELECT)

2 Answers2

Linked