Return multiple rows with carriage return, linefeed, not comma-delimited list

Question

Return multiple rows with carriage return, linefeed, not comma-delimited list

Ann M

Say Hey Kid

Points: 676
More actions
November 18, 2011 at 1:26 pm

#146144

I want to return multiple rows in one record with carriage return, linefeed, not comma-delimited. I am using XML-Path to return the string. I see plenty of ways to return the list comma-delimited. And, I have successfully implemented this solution, however when I try to replace the , with a carriage return linefeed, the characters are ignored. Or, I get results like this: And, replace doesn't seem to work. I have searched the web extensively and can't seem to come up with a suitable solution.
Thank you in advance if you are able to offer suggestions!!
:crying:

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

If you want them separated with carriage returns why can't you just return them in the rows they lie in naturally?

Assuming the above won't work, how do you know the carriage return is not present? Are you viewing your results in SSMS? You have to view those results as text, the grid will not show carriage returns. If you are viewing as text how are you adding your carriage returns?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ann M Say Hey Kid Points: 676 More actions · Answer 2

Your answer is so funny to me! Exactly what I asked. Stupid user request. I'm viewing it in SSRS 2005. They want the report to "look pretty". It's for internal consumption. WHO CARES??? They do.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

So why not just use the result set as your datasource? The user does not know, care or understand how you got the data there. Basically you have to take a collection of rows, pivot it into a single column with carriage returns to display exactly as it looks in the first place. There seems to be some detail you didn't share. Regardless, how are you adding your carriage return? Char(10)? What happens when you run this query in SSMS? Do you see the results "pivoted and carriage returned" to look like the original?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Here is an example query that will work and do what you are trying to do.

;with ReturnList (SomeValue)

as (

select 'rdwqyx41pe' union all

select 'reyewmf0wv' union all

select 'rjm6d08pmt' union all

select 'rlgv05xc40' union all

select 'ro67k4xtth' union all

select 'rq1k4jym40' union all

select 'rq633c6qby' union all

select 'rvqy776bby' union all

select 'rxjcs46ob2' union all

select 's02os7hog1' union all

select 's1ga8w2n8o' union all

select 's2mh8wa5u2'

)

SELECT top 1

Stuff((SELECT CHAR(10) + SomeValue

from ReturnList

FOR XML PATH('')), 1, 1, '')

FROM ReturnList

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/