将两个不同的列用逗号分隔开来。

Question

在 SQL Server 2012 表中，我想将两列的所有行合并为一行，仍然是两列，但每列都以逗号分隔。

例如：

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

然后之后我希望结果像这样。

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

- Stew

---

你的表格可能会有多少行？ - Martin

可能不多，最多也就三到四个。 - Stew

Answer 1

你可以像这样使用FOR XML SQL Fiddle。 查询

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

输出

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

编辑

您甚至可以使用变量将csv连接起来，这不需要像FOR XML一样进行2次表扫描，但是当与ORDER BY或其他函数在同一查询中使用时可能会遇到问题。

由于您只有3-4行，我建议采用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

- ughai

---

非常完美，运行得很好。我没有意识到我可以像那样添加第二个STUFF。每天都会学到新东西 :) - Stew

@stew STUFF是一个函数，它可以用一组新字符替换一些字符。在这种情况下，它将第一个字符替换为空的varchar。 - t-clausen.dk

比我的答案好！就像@Stew说的：每天学点新东西。 ;) 干杯 - Raging Bull

希望 SQL Server 能够得到类似于 group_concat 或 LISTAGG 的功能，这样会更加简洁和易读。 - ughai

我发布了一个（在我看来）改进了你的第二种方法的版本。 - t-clausen.dk

@t-clausen.dk 很好的方法，可以消除对 STUFF 的使用，我以前使用 ISNULL，但是 CONCAT 在内部处理了这个问题。 - ughai

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

- t-clausen.dk

---

使用 CONCAT 处理 NULL 值的巧妙方法。很酷。 - Aleksandr Fedorenko

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中的示例结果：

- Raging Bull

Answer 4

在SQL SERVER中使用FOR XML PATH

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

- Saravana Kumar

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

这可能会在大数据集上引起严重的性能问题，但我从您上面的评论中注意到，您只会有少量的行。

- Martin

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演示

- Ravi