How can I assign multiple records from a select into one parameter in sql

Question

Does anyone know how can I assign the multiple records from a select into one parameter in sql? Example:

I have the following select statement:

SELECT '|'+id+':'+rep
FROM dbo.reps

it will return the following result:

|1:tom
|2:amy
|3:ben
|4:ken

How can I assign those value into a parameter @rep and when I select @rept, it will display:

|1:tom|2:amy|3:ben|4:ken

will it possible?

Possible duplicate http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — derloopkat, Mar 23 '16 at 22:57

score 0 · Answer 1 · answered Mar 23 '16 at 22:59

One method is to use aggregate string concatenation (ala XML), but that is a hassle in SQL Server. Another is to use recursive CTEs.

The simplest method is:

DECLARE @rep varchar(max);
SET @rep = '';

SELECT @rep = (@rep + '|'+id+':'+rep0
FROM dbo.reps;

I am not sure if this is guaranteed to be supported (I haven't seen documentation that explicitly says yes or no on this), but it does generally work in SQL Server.

How can I assign multiple records from a select into one parameter in sql

1 Answers1