Append records of two columns

Question

How can I append records of two fields in single row.

Let's say, we have two columns in a table containing n number of records. I need to append each row having comma delimited in a single row.

Col1
Abs
Abd
Abf
Abg

Desired output

O/pcol
Abs:10 ;Abd:15 ;Abf:20 ;Abg:0

I hope this helps.

Is this what you want? SELECT FIELD1 + ', ' + FIELD2 FROM YOURTABLE — NicoRiff, Feb 01 '17 at 18:33
Re-writing the question might be a good idea. It's quite difficult to understand what you're trying to say. With proper examples, of course. — James Z, Feb 01 '17 at 18:34
Possible duplicate http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — DiegoS, Feb 01 '17 at 18:37
Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — DiegoS, Feb 01 '17 at 18:37

score 1 · Answer 1 · answered Feb 03 '17 at 09:23

You can use an "accumulator" variable to concatenate all the values:

declare @testTable table (Col1 nvarchar(50),Col2 nvarchar(50))
declare @accumulator nvarchar(max)

insert into @testTable
          select 'Abs',10
union all select 'Abd',15
union all select 'Abf',20
union all select 'Abg',0

set @accumulator =''

select @accumulator = @accumulator + Col1 + ':' + Col2 + ' ;' from @testTable

select @accumulator

The output of this snippet should be:

Abs:10 ;Abd:15 ;Abf:20 ;Abg:0 ;

Append records of two columns

1 Answers1