Help with SELECT STUFF and FOR XML to include Carriage Return for Null records

Question

Help with SELECT STUFF and FOR XML to include Carriage Return for Null records

tskelley

SSCommitted

Points: 1870
More actions
April 4, 2017 at 3:39 pm

#336784

Hi everyone,
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:
Null
Value1
Null
Value2
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,
Tom

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Carriage returns and line feeds together make for a standard ASCII based line ending. Not sure if it will help, but wherever CHAR(10) appears, it probably should be CHAR(13) + CHAR(10).

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

tskelley SSCommitted Points: 1870 More actions · Answer 2

Hi, and thank you so much for taking the time to reply. It reminded me about my original post!

I was able to cobble a solution together, ugly, but works. I will post below if anyone else runs into this. I ended up using the REPLACE command to catch the extra characters I did not want, because it appeared they were coming back as strings instead of being recorded as carriage returns. The new code is in bold.

SELECT REPLACE(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,'') ,' ','')

sgmunson SSC Guru Points: 110640 More actions · Answer 3

One last thought... carriage return is actually CHAR(13), not CHAR(10). However, in the world of ASCII, they typically appear in order CHAR(13) followed by CHAR(10). The latter is the New Line (aka Line Feed) character. Wondering if the tool being used to "see" the data was impacted by the interpretation?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

drew.allen SSC Guru Points: 76985 More actions · Answer 4

The following approach is better, because there are other characters that may be entitized in XML and this approach will handle all of them in a single step.

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(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'')

The TYPE directive ensures that the value is returned as XML so that you can use the .value method to convert to nvarchar (including converting entitized characters back to a more legible form).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

tskelley SSCommitted Points: 1870 More actions · Answer 5

Drew: Thank you, I was noticing that when I was researching on the web.

Steve: I am displaying the view in a custom ActiveReports project/viewer. And fortunately, it rendered fine with the hack. I know I had tried CHAR(13) earlier, but I will play around with it a bit more and let you know if anything works better (cleaner).

NedaYar Newbie Points: 1 More actions · Answer 6

LineBreak does not work in STUFF. You can use this trick :

SELECT REPLACE( STUFF((SELECT ISNULL(t2.Column2_Name, '') + '##@@$$$'
    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,'') , '##@@$$$', char(13)+Char(10))