A comma separates two separate columns

Question

In a SQL Server 2012 table, I want to take all the rows from two columns and turn them into one row, two more columns, but each column is separated by commas.

for instance

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

Then after that id, like the results, will look like this

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

Answer 1

you can use FOR XMLas 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 a variable to combine csv together, which does not require two table scans, such as FOR XML, however, when used with ORDER BYor other functions in the same query, you may experience problems with it.

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

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

, CONCAT, 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

Answer 3

:

SELECT * FROM
(SELECT Customerid = STUFF((
          SELECT ',' + CAST(md.Customerid as varchar(max))
          FROM dbo.TableName md
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        Facilityid=STUFF((
          SELECT ',' + CAST(md.Facilityid as varchar(max))
          FROM dbo.TableName md
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.TableName m
JOIN dbo.TableName s ON s.customerid = m.customerid AND s.facilityid=m.facilityid) as Tbl
GROUP BY customerid,facilityid

:

Customerid  Facilityid
--------------------------
1,2,5       5678,9101,6543

SQL Fiddle

Answer 4

FOR XML PATH SQL SERVER

SELECT (SELECT CAST(Customerid AS VARCHAR) + ',' from 
                   Table for XML PATH(''), type).value('.', 'varchar(max)') AS Customerid, 
    (SELECT CAST(FacilityId AS VARCHAR) + ',' from 
                   Table for XML PATH(''), type).value('.', 'varchar(max)') AS FacilityId

Answer 5

STUFF FOR XML:

CREATE TABLE #tmpDemo (CustomerId INT, FacilityId INT)
INSERT INTO #tmpDemo VALUES (1, 5678), (2, 9101), (5, 6543)

SELECT  DISTINCT
        STUFF((
          SELECT ',' + CAST(T.CustomerId AS VARCHAR)
          FROM #tmpDemo T
          FOR XML PATH('')), 1, 1, '') as CustomerId,
        STUFF((
          SELECT ',' + CAST(T.FacilityId AS VARCHAR)
          FROM #tmpDemo T
          FOR XML PATH('')), 1, 1, '') as FacilityId
FROM #tmpDemo m

DROP TABLE #tmpDemo

, , .

Answer 6

COALESCE

DECLARE @Names VARCHAR(8000) 
DECLARE @id VARCHAR(8000) 

SELECT @id = COALESCE(@id + ', ', '') + CAST(col1 AS VARCHAR),@Names = COALESCE(@Names + ', ', '') + col2
FROM tab1

select @id,@Names

SQLFiddle Demo