SQL query for a carriage return

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query for a carriage return

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2014-01-06 : 17:20:00

Hi,
I want to list the results for SponsorName separate by carriage return instead of comma(,) but not sure how to do it, can some one please help. thx

select id , stuff((select ','+ ' '+[fname] + ' ' + [lname] from sponsor s where r.id = s.request_id for xml path('')),1,1,'')as 'SponsorName'
from [dbo].[request]r

currently, it shows:
ID SponsorName
==== ===========
1 John Langston, Cory Duke, Jenifer Ta
2 Mike Steve
3 Andy Miller, David Berleth

but I would like to have the results show below

ID SponsorName
==== ===========
1 John Langston
Cory Duke
Jenifer Ta
2 Mike Steve
3 Andy Miller
David Berleth

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-06 : 17:34:57

Replace(SponsorName, ',', char(13))

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

kt
Yak Posting Veteran

88 Posts

Posted - 2014-01-06 : 17:42:12

already tried this but didn't work.

Replace(stuff((select ','+ ' '+[fname] + ' ' + [lname] from sponsor s where r.id = s.requestor_id for xml path('')),1,1,'') , ',', CHAR(13) + CHAR(10)) as 'SponsorName'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-07 : 00:49:50

this will work when you results to text option.


select id , stuff((select CHAR(13) + CHAR(10)+[fname] + ' ' + [lname] from sponsor s where r.id = s.request_id for xml path('')),1,2,'')as 'SponsorName'
from [dbo].[request]r

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources