Comma separate two separate columns

Question

In an SQL Server 2012 table I want to take all the rows from two columns and turn them into one row, still two columns, but each column being comma-separated.

For example

Customerid    |    FacilityId
-----------------------------
1                    5678
2                    9101
5                    6543

Then afterwards id like the results to be like this

Customerid    |    FacilityId
-----------------------------
1,2,5            5678,9101,6543

Answer 1

you can use FOR XML like this SQL Fiddle

Query

SELECT STUFF((
SELECT ',' + CONVERT(VARCHAR(10),Customerid)
FROM Customer
FOR XML PATH('')),1,1,'') as Customerid,
STUFF((
SELECT ',' + CONVERT(VARCHAR(10),FacilityId)
FROM Customer
FOR XML PATH('')),1,1,'') as FacilityId

Output

Customerid  FacilityId
1,2,5   5678,9101,6543

EDIT

You can even use variable to concatenate the csv together which doesn't require 2 table scans like the FOR XML however you may encounter issues with it when using with ORDER BY or other functions in the same query.

Since you have only 3-4 rows, I would suggest going with FOR XML approach

DECLARE @Customerid VARCHAR(MAX) = '',@FacilityId VARCHAR(MAX) = ''

SELECT 
@Customerid += ',' + CONVERT(VARCHAR(10),Customerid),
@FacilityId += ',' + CONVERT(VARCHAR(10),FacilityId)
FROM Customer

SELECT STUFF(@Customerid,1,1,'') as Customerid, STUFF(@FacilityId,1,1,'') as FacilityId

Answer 2

Here is a simple and fast way using CONCAT, it will work from sqlserver 2012:

DECLARE @t table(Customerid int, FacilityId int)
INSERT @t values(1,5678),(2,9101),(5,6543)

DECLARE @x1 varchar(max), @x2 varchar(max)

SELECT 
  @x1 = concat(@x1 + ',', Customerid), 
  @x2 = concat(@x2 + ',', FacilityId)
FROM @t

SELECT @x1, @x2