aaehluuoibiixjecw
New Member
I think I need to better understand the FOR XML PATH to get this working. Maybe you can help me with this simple example:Given\[code\]DECLARE @p VARCHAR(200)DECLARE @phn TABLE (Phone VARCHAR(25))INSERT INTO @phn VALUES('204-721-1532')INSERT INTO @phn VALUES('306-922-2058')INSERT INTO @phn VALUES('204-728-3852')\[/code\]I want to change all the rows in @phn.Phone into a VARCHAR, which, if it were only Python, would be something like\[code\]SET @p = ', '.join(@phn.Phone) # Alas\[/code\]Now, THIS works up to a point:\[code\]SELECT DISTINCT Phone+', ' FROM @phn FOR XML PATH('')==> 204-721-1532, 204-728-3852, 306-922-2058, \[/code\]There's that trailing comma, but no problem there. What I need is to get this result into a VARCHAR variable, something like\[code\]SELECT DISTINCT @p = Phone+', ' FROM @phn FOR XML PATH('')\[/code\]But that's not allowed (Msg 6819, Level 16, State 3, Line 45/The FOR XML clause is not allowed in a ASSIGNMENT statement.)Eventually, this is going to go into a scalar function, and I want the function to RETURN the joined up string.