如何创建SQL Server函数以将子查询中的多行"联接"到单个定界字段中？
为了说明，假设我有两个表，如下所示：
VehicleID Name 1 Chuck 2 Larry LocationID VehicleID City 1 1 New York 2 1 Seattle 3 1 Vancouver 4 2 Los Angeles 5 2 Houston
我想编写一个查询以返回以下结果：
VehicleID Name Locations 1 Chuck New York, Seattle, Vancouver 2 Larry Los Angeles, Houston
我知道可以使用服务器端游标完成此操作，即：
DECLARE @VehicleID int
DECLARE @VehicleName varchar(100)
DECLARE @LocationCity varchar(100)
DECLARE @Locations varchar(4000)
DECLARE @Results TABLE
(
VehicleID int
Name varchar(100)
Locations varchar(4000)
)
DECLARE VehiclesCursor CURSOR FOR
SELECT
[VehicleID]
, [Name]
FROM [Vehicles]
OPEN VehiclesCursor
FETCH NEXT FROM VehiclesCursor INTO
@VehicleID
, @VehicleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Locations = ''
DECLARE LocationsCursor CURSOR FOR
SELECT
[City]
FROM [Locations]
WHERE [VehicleID] = @VehicleID
OPEN LocationsCursor
FETCH NEXT FROM LocationsCursor INTO
@LocationCity
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Locations = @Locations + @LocationCity
FETCH NEXT FROM LocationsCursor INTO
@LocationCity
END
CLOSE LocationsCursor
DEALLOCATE LocationsCursor
INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations
END
CLOSE VehiclesCursor
DEALLOCATE VehiclesCursor
SELECT * FROM @Results
但是，如我们所见，这需要大量代码。我想要的是一个通用函数，它将允许我执行以下操作：
SELECT VehicleID
, Name
, JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles
这可能吗？还是类似的东西？
解决方案
回答
我不相信有一种方法可以在一个查询中做到这一点，但是我们可以使用一个临时变量来玩这样的技巧：
declare @s varchar(max) set @s = '' select @s = @s + City + ',' from Locations select @s
它绝对比在游标上移动更少的代码，并且可能更有效。
回答
如果使用的是SQL Server 2005，则可以使用FOR XML PATH命令。
SELECT [VehicleID]
, [Name]
, (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX))
FROM [Location]
WHERE (VehicleID = Vehicle.VehicleID)
FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]
它比使用游标容易得多，并且似乎运行良好。
回答
请注意，Matt的代码将在字符串末尾产生一个逗号。如Lance的帖子中的链接所示，使用COALESCE(或者ISNULL)使用类似的方法，但不会给我们留下多余的逗号。为了完整起见，下面是sqlteam.com上Lance的链接中的相关代码：
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
回答
如果运行的是SQL Server 2005，则可以编写自定义CLR聚合函数来处理此问题。
转换：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSV:IBinarySerialize
{
private StringBuilder Result;
public void Init() {
this.Result = new StringBuilder();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) return;
this.Result.Append(Value.Value).Append(",");
}
public void Merge(CSV Group) {
this.Result.Append(Group.Result);
}
public SqlString Terminate() {
return new SqlString(this.Result.ToString());
}
public void Read(System.IO.BinaryReader r) {
this.Result = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w) {
w.Write(this.Result.ToString());
}
}
回答
下面的代码将适用于Sql Server 2000/2005/2008
CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @csvCities VARCHAR(1000)
SELECT @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'')
FROM Vehicles
WHERE VehicleId = @VehicleId
return @csvCities
END
-- //Once the User defined function is created then run the below sql
SELECT VehicleID
, dbo.fnConcatVehicleCities(VehicleId) AS Locations
FROM Vehicles
GROUP BY VehicleID
回答
版本注意：对于此解决方案，我们必须使用SQL Server 2005或者更高版本，并且"兼容级别"设置为90或者更高。
请参阅此MSDN文章，以获取创建用户定义的聚合函数的第一个示例，该函数将从表中的列获取的一组字符串值连接在一起。
我谦虚的建议是忽略添加的逗号，以便我们可以使用自己的临时定界符(如果有)。
参考示例1的Cversion：
change: this.intermediateResult.Append(value.Value).Append(',');
to: this.intermediateResult.Append(value.Value);
和
change: output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
to: output = this.intermediateResult.ToString();
这样，当我们使用自定义聚合时，可以选择使用自己的定界符，或者根本不使用定界符，例如：
SELECT dbo.CONCATENATE(column1 + '|') from table1
注意：请小心我们尝试处理的聚合中的数据量。如果尝试连接成千上万的行或者许多非常大的数据类型，则可能会收到.NET Framework错误，指出"缓冲区不足。"
回答
从我可以看到的" FOR XML"(如前所述)中，如果我们还希望像OP一样选择其他列(我想大多数人会这样做)，则是这样做的唯一方法。
使用COALESCE(@var ...不允许包含其他列。
更新：
多亏了programmingsolutions.net，有一种方法可以删除"尾随"逗号。
通过将其设置为前导逗号并使用MSSQL的" STUFF"功能，我们可以将第一个字符(前导逗号)替换为空字符串，如下所示：
stuff(
(select ',' + Column
from Table
inner where inner.Id = outer.Id
for xml path('')
), 1,1,'') as Values
回答
在SQL Server 2005中
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
在SQL Server 2016中
我们可以使用FOR JSON语法
IE。
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
结果将成为
Id Emails 1 [email protected] 2 NULL 3 [email protected], [email protected]
即使数据包含无效的XML字符，这也将起作用
'"}，{""："'是安全的，因为如果数据包含'"}，{""："'，它将被转义为"}，{" _ "："
我们可以将"，"替换为任何字符串分隔符
在SQL Server 2017中，Azure SQL数据库
我们可以使用新的STRING_AGG函数
