我正在尝试实现以下目标:

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];


评论


谢谢亚伦。我当前的解决方案与您的解决方案几乎相同,除了我使用的是DISTINCT而不是GROUP BY。

–user2732180
2014年6月12日在3:49

@ user2732180您应该使用GROUP BY,因为每个状态执行一次连接的可能性更大。例如,使用DISTINCT,它将对加利福尼亚的每个实例应用相同的串联,然后才丢弃它生成这些重复项的所有工作。

–亚伦·伯特兰(Aaron Bertrand)
2014年6月12日在11:41



#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)在堆栈溢出时的莫名其妙行为