I'm unabashedly copying Listing 7-8 from this book and modifying it to run not as a stored proc, but as the source for a SRS project. The segment I'm struggling with creates a "column list" from the distinct rows that I'm going to pivot out to columns. After the variables are created and SET, the resulting code crashes:
(SELECT N',' + QUOTENAME(pivot_col) AS [text()]
FROM (SELECT DISTINCT(Variable.VariableName) AS pivot_col
VariableValue Vv Inner Join
Variable On Variable.VariableID = Vv.VariableID
Inner Join Projects p On p.ProjectID = Vv.ProjectID
Vv.ProjectID In (Select
Projects Inner Join
ProjectTree On Projects.ProjectID = ProjectTree.ChildProject
ProjectTree.ProjectID = 37)
) AS Query) AS DistinctCols
ORDER BY pivot_col
FOR XML PATH('')),
1, 1, N'');
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Where'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'.
I believe if I can fix this statement so that it will run, I can back out the fix to the original variables. Any thoughts is muchly appreciated!