sql-server – 如何使用COALESCE多行而不使用逗号？

California | Los Angeles,San Francisco,Sacramento
Florida    | Jacksonville,Miami

不幸的是,我正在“,洛杉矶,旧金山,萨克拉门托,杰克逊维尔,迈阿密”

我可以使用STUFF函数实现我想要的结果,但是想知道是否有更简洁的方法使用COALESCE？

STATE       | CITY
California  | San Francisco
California  | Los Angeles
California  | Sacramento
Florida     | Miami
Florida     | Jacksonville 


DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col,'') + ',' + city
FROM tbl where city = 'California';
SELECT @col;

谢谢

解决方法

DECLARE @col nvarchar(MAX);
SELECT @col = COALESCE(@col + ',','') + city
  FROM dbo.tbl WHERE state = 'California';

当然,这仅适用于填充每个状态的变量.如果您一次拉出每个州的列表,则可以一次性获得更好的解决方案：

SELECT [state],cities = STUFF((
    SELECT N',' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    FOR XML PATH(''),TYPE).value(N'.[1]',N'nvarchar(max)'),1,2,N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

结果：

state       cities
----------  --------------------------------------
California  San Francisco,Los Angeles,Sacramento  
Florida     Miami,Jacksonville

要在每个州内按城市名称订购：

SELECT [state],' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    ORDER BY city
    FOR XML PATH(''),N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

在Azure SQL数据库或SQL Server 2017中,您可以使用the new STRING_AGG() function：

SELECT [state],cities = STRING_AGG(city,N',')
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];

并按城市名称排序：

SELECT [state],') 
                         WITHIN GROUP (ORDER BY city)
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!