我正在努力实现以下目标：
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];
