I am working on an HTTP POST application where I am using SQL to return "message" based text results.

A typical query would yield a message something like:

135,15,1,1,1,4,1,1,1,"NAME"

I now have a requirement to have the same query return several rows, but when i send them, they need to be a single message containing several records delimited by CRLF, yielding (1) message, so the result set would look like:

135,15,1,1,1,4,1,1,1,"NAME"

135,15,2,1,1,2,2,3,2,"TestName"
135,15,3,1,1,4,1,1,1," 41"
135,15,4,1,1,4,1,1,1,"asd"

Any idea how to do this?

You would need to concatenate your rows using CRLF (CHAR(13) + CHAR(10)) character using XML PATH('') approach. This blog http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html explains this technique in details. Please read it carefully as there is an important point close to the middle of the blog which applies to your case.

Hi Naomi,

Good link. Just to make it more specific.

DECLARE @T TABLE(LINE VARCHAR(100))

INSERT INTO @T 
VALUES
('135,15,1,1,1,4,1,1,1,"NAME"'),
('135,15,2,1,1,2,2,3,2,"TestName"'),
('135,15,3,1,1,4,1,1,1," 41"'),
('135,15,4,1,1,4,1,1,1,"asd"')

DECLARE @CRLF VARCHAR(2)=CHAR(13)+CHAR(10)
DECLARE @TXT VARCHAR(2000)

select @TXT=stuff(
              (
                select @CRLF+Line
                from @T 
                for xml path(''),type
              ).value('.','varchar(max)')
              ,1,1,'')

PRINT @TXT

Thanks!