Convert rows to string on SQL

sql-server, sql-server-2008, sql-server-2008-r2

Solution

As a single column and a single row you can do this

DECLARE @out as varchar(max)

SET @Out = ''

SELECT @Out = @Out +  [User] + ' says: ' + Observation + CHAR(13) + CHAR(10)  
FROM Table1

SELECT @out

See it here

here's the output in SSMS (using text output)

-------------------------------
John says: This is correct!
Michael says: I got an error!
Joshua says: This is incorrect!

(1 row(s) affected)

Problem

I have a table on Sql: ``` ID User Observation ======================================== 1 John This is correct! ---------------------------------------- 2 Michael I got an error! ---------------------------------------- 3 Joshua This is incorrect! ---------------------------------------- ``` What I want is a function thar returns a varchar With the data on a string Like this: Edit: This is the result I expect: ``` John says: This is correct!\r\nMichael says: I got an error!\r\nJoshua says: This is incorrect ``` Is there a way to do that?

Original source