Saturday, May 30, 2015

Using Stuff and For XML Path to format the string in one column

Recently in one project I needed to create some SSRS reports to present business data. One normal requirement is to use comma to join all the text in one column to one big string. I had experience to use Stuff and For XML Path to join columns, but this time I had a chance to know what is really going on behind For XML Path.  You can check this great blog for details to explain the syntax.

In my project, the first requirement was to join 2 columns with a space, and use a comma to separate each record, but leave a space after the comma.  So my SQL was like this:

stuff((
                    select ', ' + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '')


Later, the requirement changed.  User wanted to show each record in a separate line.  So I needed to replace the comma with a line feed.  Char(13) and Char(10) together can make a new line.  In my case, Char(10) is good enough to go.  So I changed my SQL to this:

stuff((
                    select Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 1, '')

Later, user still wanted to add the comma before the line break, so I just slightly changed to this:

stuff((
                    select ',' + Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '')
 


So now in my report, I can have a formatted string in multiple line.

No comments: