I have been struggling with this T-SQL for much too long today. I am using the SELECT STUFF with FOR XML to present multiple rows into one field. However, when I Left Join a table and get Null results for some rows, I am unable to get the carriage return to work correctly.
For example, my return result set is:
I would like my query to put a carriage return for the first Null, then Value1 and carriage return, then carriage return for the next Null, and then the final Value2, thus keeping the spacing of the result set, which I will need to display another row that does have all four values. Essentially, I am trying to handle a subreport functionality by concatenating the results into one row.
My sample query is:
SELECT STUFF((SELECT ISNULL(t2.Column2_Name, '') + CHAR(10)
FROM [dbo].[Table1] t1
LEFT JOIN Table2 t2 ON t1.Column1_ID = t2.Column2_ID
ORDER BY t1.Column1_ID
FOR XML PATH('')),1,0,'')
The Left Join produces Nulls in the results, and I need to be able to keep results spaced appropriately with carriage returns.
Any help would be greatly appreciated,