T-SQL Help - Dynamic Pivot

Last Post 03 Oct 2008 10:05 AM by PerryWhite. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
PerryWhite
New Member
New Member

--
03 Oct 2008 10:05 AM
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:

STUFF(
(SELECT N',' + QUOTENAME(pivot_col) AS [text()]
FROM (SELECT DISTINCT(Variable.VariableName) AS pivot_col
FROM(
VariableValue Vv Inner Join
Variable On Variable.VariableID = Vv.VariableID
Inner Join Projects p On p.ProjectID = Vv.ProjectID
Where
Vv.ProjectID In (Select
Projects.ProjectID
From
Projects Inner Join
ProjectTree On Projects.ProjectID = ProjectTree.ChildProject
Where
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!

Regards,
Perry
You are not authorized to post a reply.

Acceptable Use Policy