Concatenate multiple rows to one row in SQL Server

Question

I have a SQL Server output which returns the following values with one column:

RepresentID 
---------------
111122222
3333344444
5555566666
000000090909
7777788888
9999999999
121212131313
141414151515

Output :

RepresentID
---------------
111122222
3333344444
5555566666
000000090909
7777788888
9999999999
121212131313
141414151515

https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv — Hussein Salman, Mar 28 '18 at 03:53

TheGameiswar · Answer 1 · 2018-03-28T03:51:05.623

1

need to concatenate all the rows in to one row.

use xml path like below

 SELECT STUFF((
        SELECT
            ','+cast(representid as varchar(30))
        FROM
          table 
        FOR
            XML PATH('')
        ),1,1,''
    ) AS comma_seperated_list;

On sql2017 , you can use string_aggregate like below

select string_agg(cast(representid as varchar(40)),',')
from table

edited Mar 28 '18 at 03:51

answered Mar 28 '18 at 03:45

TheGameiswar

27,855
8
56
94

This is a good solution, but to get a line break between each element instead of a comma-separated list, you can use replace the comma with CHAR(13) (carriage return), CHAR(10) (line feed), or CHAR(13) + CHAR(10) (CRLF). – Max Szczurek Mar 28 '18 at 04:16

Concatenate multiple rows to one row in SQL Server

1 Answers1