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.
