Convert rows to string on SQL

Question

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?

I've tried using a cursor but I don't want to do it that way — jcvegan
– jcvegan, Commented Apr 19, 2012 at 20:01

Conrad Frix · Accepted Answer · 2012-04-19 20:16:55Z

7

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)

edited Apr 19, 2012 at 20:16

answered Apr 19, 2012 at 20:08

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jcvegan Over a year ago

Out has many rows if the table has many rows.. I want only ONE row containing all the data.

Conrad Frix Over a year ago

um no it gives back one row with line breaks. that's what CHAR(13) + CHAR(10) does. Also SELECT @out can't ever return more than one row

Tim Schmelter · Accepted Answer · 2012-04-19 20:14:14Z

4

If you want to concat all rows separated by a carriage-return+new-line:

declare @UserObservation Table(ID int, [User] varchar(20), Observation varchar(100));
insert into @UserObservation values(1, 'John' ,'This is correct!');
insert into @UserObservation values(2, 'Michael' ,'I got an error!');
insert into @UserObservation values(3, 'Joshua' ,'This is incorrect!');

DECLARE @WhatAllUserSay VARCHAR(8000) ;
SELECT @WhatAllUserSay = 
    COALESCE(@WhatAllUserSay + CHAR(13) + CHAR(10), '') + [User] + ' says: ' + Observation 
FROM @UserObservation

PRINT @WhatAllUserSay;

COALESCE

Output:

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

edited Apr 19, 2012 at 20:14

answered Apr 19, 2012 at 20:02

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

5 Comments

jcvegan Over a year ago

But this give me many rows containing strings.... what I want is a single row with all the data separating rows by a break line.

Vince Pergolizzi Over a year ago

Please give an example of the EXACT output you require, then people will be able to help you (or at least tell you confidently that its not possible).

jcvegan Over a year ago

Updated... That is te result I expect

Conrad Frix Over a year ago

nice use of COALESCE to avoid initing the string.

huhu78 Over a year ago

COALESCE is not required in fact if you initialize your VARCHAR with ''. Great trick with ~"SELECT @WhatAllUserSay += nextUserSays FROM MultipleRowTable". I always thought that the only way to concatenate string from different rows is by using CURSOR and loop. Very very useful.

Seth Flowers · Accepted Answer · 2012-04-19 20:02:37Z

0

SELECT [User] + ' says: ' + [Observation] FROM [TableName]

answered Apr 19, 2012 at 20:02

Seth Flowers

9,2302 gold badges34 silver badges46 bronze badges

Comments

Teja · Accepted Answer · 2012-04-19 20:02:49Z

0

SELECT User +' says: ' + Observation AS USER_SAID
FROM TABLE;

answered Apr 19, 2012 at 20:02

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Collectives™ on Stack Overflow

Convert rows to string on SQL

4 Answers 4

2 Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related