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;
谢谢
#1 楼
这可能是您追求的更清洁的方法。基本上,检查变量是否已经初始化。如果还没有,请将其设置为空字符串,然后附加第一个城市(不带逗号)。如果有的话,则添加逗号,然后添加城市。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], cities = STUFF((
SELECT N', ' + city FROM dbo.tbl
WHERE [state] = x.[state]
ORDER BY city
FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];
在Azure SQL数据库或SQL Server 2017+中,可以使用新的
STRING_AGG()
函数:SELECT [state], cities = STRING_AGG(city, N', ')
FROM dbo.tbl
GROUP BY [state]
ORDER BY [state];
并按城市名称排序:
SELECT [state], cities = STRING_AGG(city, N', ')
WITHIN GROUP (ORDER BY city)
FROM dbo.tbl
GROUP BY [state]
ORDER BY [state];
#2 楼
只是要添加到上述Aaron的答案中...请注意,
ORDER BY
可能会因为只包含查询中的最后一项而中断。就我而言,我没有分组,因此不确定是否有区别。我正在使用SQL2014。就我而言,我有类似value1,value2,value3 ...的内容,但变量中的结果仅为value3。Aaron评论说:
在Connect上已至少报告了四次:
在变量并置和按过滤器排序的结果中(如条件)
通过CROSS APPLYs从有序SELECT分配局部变量并且表值函数仅返回最后一个值
串联varchar(max)/ nvarchar(max )表变量中的值,如果通过非主键列进行过滤和排序,则可能会返回不正确的结果。
来自Microsoft的示例响应:
您看到的行为是设计使然。在带有ORDER BY子句的查询中使用赋值操作(在此示例中为串联)具有未定义的行为。
响应还引用KB 287515:
PRB:执行计划串联查询的结果和结果取决于表达式的位置
解决方案是,如果串联的顺序很重要,并且当然要确定的话,请使用
FOR XML PATH
(Aaron回答中的第二种方法)包括所有值。另请参见:nvarchar串联/索引/ nvarchar(max)在堆栈溢出时的莫名其妙行为
评论
谢谢亚伦。我当前的解决方案与您的解决方案几乎相同,除了我使用的是DISTINCT而不是GROUP BY。
–user2732180
2014年6月12日在3:49
@ user2732180您应该使用GROUP BY,因为每个状态执行一次连接的可能性更大。例如,使用DISTINCT,它将对加利福尼亚的每个实例应用相同的串联,然后才丢弃它生成这些重复项的所有工作。
–亚伦·伯特兰(Aaron Bertrand)
2014年6月12日在11:41