T-SQL 2008: concatenate rows using for xml path and new line feed carriage

Question

I have the following table with the following values:

create table test 
(
    folder nvarchar(20),
    size int
)

Video   255
Music   255
Docs    255
Papers  255

I need to concatenate the folder field and to be separated by comma and a new line. So far, I have this:

select distinct folder + ','   as [text()]
from  test
for xml path('')

This will concatenate with separated by ',' but I cannot get the new line to work using char(13).

Answer 1

Not 100% clear on your expected results, but here is a little hack

Declare @Test table (folder nvarchar(20),size int)
Insert Into @Test values
('Video' ,255),
('Music' ,255),
('Docs'  ,255),
('Papers',255)

Select [text()] = Replace(cast([text()] as nvarchar(max)),'||',char(13)+char(10))
 From (Select [text()] = (Select Distinct folder + '||' From @test For XML path('') ) ) A

Returns

text()
Docs
Music
Papers
Video

Another one could be

Declare @String varchar(max) = ''
Select @String=@String+Folder+char(13)+char(10)
  From @Test

Select [text()]=@String

Returns

text()
Video
Music
Docs
Papers