如何创建一个SQL Server函数来“join”;子查询中的多行进入单个分隔字段?

为了说明这一点，假设我有如下两个表:

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

这可能吗?或者类似的东西?

163657 次浏览
小开

我不相信有一种方法可以在一个查询中完成它，但你可以用一个临时变量来玩这样的技巧:

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]

这比使用光标容易得多，而且似乎工作得相当好。

更新

对于那些在新版本的SQL Server中仍然使用这种方法的人来说，还有另一种方法来做它，它更简单，性能更好

. STRING_AGG方法，自SQL Server 2017以来已经可用 
SELECT  [VehicleID]
,[Name]
,(SELECT STRING_AGG([City], ', ')
FROM [Location]
WHERE VehicleID = V.VehicleID) AS Locations
FROM   [Vehicle] V

这还允许将不同的分隔符指定为第二个参数，比前一种方法提供了更多的灵活性。

小开

注意，马特的代码将导致在字符串的末尾增加一个逗号;使用COALESCE(或ISNULL)，如Lance的链接中所示，使用了类似的方法，但没有给你留下一个额外的逗号。为了完整起见，下面是兰斯在sqlteam.com上的相关代码:

DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
小开

如果你正在运行SQL Server 2005，你可以写一个自定义CLR聚合函数来处理这个问题。

c#版本:

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的c#版本:

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错误，说明“[t]他缓冲区不足。”

小开

从我可以看到FOR XML(如前所述)是唯一的方法，如果你想也选择其他列(我猜大多数会)作为OP。 使用COALESCE(@var...不允许包含其他列 < p >更新: 感谢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语法

即。

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   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

即使数据包含无效的XML字符，这也可以工作

”},{:“":"'是安全的，因为如果您的数据包含'"}，{"’,它将逃到 "},{\"_\":\"

你可以用任何字符串分隔符替换'，'

在SQL Server 2017, Azure SQL数据库

你可以使用新的STRING_AGG函数

小开

在单个SQL查询中，不使用FOR XML子句 公共表表达式用于递归地连接结果 

-- rank locations by incrementing lexicographical order
WITH RankedLocations AS (
SELECT
VehicleID,
City,
ROW_NUMBER() OVER (
PARTITION BY VehicleID
ORDER BY City
) Rank
FROM
Locations
),
-- concatenate locations using a recursive query
-- (Common Table Expression)
Concatenations AS (
-- for each vehicle, select the first location
SELECT
VehicleID,
CONVERT(nvarchar(MAX), City) Cities,
Rank
FROM
RankedLocations
WHERE
Rank = 1


-- then incrementally concatenate with the next location
-- this will return intermediate concatenations that will be
-- filtered out later on
UNION ALL


SELECT
c.VehicleID,
(c.Cities + ', ' + l.City) Cities,
l.Rank
FROM
Concatenations c -- this is a recursion!
INNER JOIN RankedLocations l ON
l.VehicleID = c.VehicleID
AND l.Rank = c.Rank + 1
),
-- rank concatenation results by decrementing length
-- (rank 1 will always be for the longest concatenation)
RankedConcatenations AS (
SELECT
VehicleID,
Cities,
ROW_NUMBER() OVER (
PARTITION BY VehicleID
ORDER BY Rank DESC
) Rank
FROM
Concatenations
)
-- main query
SELECT
v.VehicleID,
v.Name,
c.Cities
FROM
Vehicles v
INNER JOIN RankedConcatenations c ON
c.VehicleID = v.VehicleID
AND c.Rank = 1
小开

我通过创建以下函数找到了一个解决方案:

CREATE FUNCTION [dbo].[JoinTexts]
(
@delimiter VARCHAR(20) ,
@whereClause VARCHAR(1)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Texts VARCHAR(MAX)


SELECT  @Texts = COALESCE(@Texts + @delimiter, '') + T.Texto
FROM    SomeTable AS T
WHERE   T.SomeOtherColumn = @whereClause


RETURN @Texts
END
GO

用法:

SELECT dbo.JoinTexts(' , ', 'Y')
小开

试试这个查询

SELECT v.VehicleId, v.Name, ll.LocationList
FROM Vehicles v
LEFT JOIN
(SELECT
DISTINCT
VehicleId,
REPLACE(
REPLACE(
REPLACE(
(
SELECT City as c
FROM Locations x
WHERE x.VehicleID = l.VehicleID FOR XML PATH('')
),
'</c><c>',', '
),
'<c>',''
),
'</c>', ''
) AS LocationList
FROM Locations l
) ll ON ll.VehicleId = v.VehicleId
小开

对于其他答案，阅读答案的人必须知道车辆表，并创建车辆表和数据来测试解决方案。

下面是一个使用SQL Server“Information_Schema”的例子。表列”。通过使用该解决方案，不需要创建表或添加数据。这个示例为数据库中的所有表创建一个以逗号分隔的列名列表。

SELECT
Table_Name
,STUFF((
SELECT ',' + Column_Name
FROM INFORMATION_SCHEMA.Columns Columns
WHERE Tables.Table_Name = Columns.Table_Name
ORDER BY Column_Name
FOR XML PATH ('')), 1, 1, ''
)Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
小开
Mun的答案对我来说不适用，所以我对这个答案做了一些修改，让它起作用。希望这能帮助到一些人。 SQL Server 2012:

.使用实例 
SELECT [VehicleID]
, [Name]
, STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,City)
FROM [Location]
WHERE (VehicleID = Vehicle.VehicleID)
FOR XML PATH ('')), 1, 2, '') AS Locations
FROM [Vehicle]

提交答案