如何创建 SQL Server 函数来“连接” 将子查询中的多行放入单个分隔字段中？

Question

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

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

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

Answer 1

请注意，Matt 的代码 会导致字符串末尾出现一个额外的逗号； 使用 COALESCE（或 ISNULL）（如 Lance 帖子中的链接所示）使用类似的方法，但不会留下额外的逗号需要删除。 为了完整起见，以下是 sqlteam.com 上 Lance 链接中的相关代码：

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

Answer 2

如果您使用的是 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 中使用此方法的任何人，还有另一种方法，使用
< code>STRING_AGG 方法自 SQL Server 2017 起可用。

SELECT  [VehicleID]
       ,[Name]
       ,(SELECT STRING_AGG([City], ', ')
         FROM [Location]
         WHERE VehicleID = V.VehicleID) AS Locations
FROM   [Vehicle] V

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

Answer 3

据我所知，如果您还想像OP那样选择其他列（我猜大多数人都会这样做），那么FOR XML（如之前发布的）是唯一的方法。
使用 COALESCE(@var... 不允许包含其他列。

更新：
感谢 programmingsolutions.net 有一种方法可以删除“尾随”逗号。
通过将其设置为前导逗号并使用 MSSQL 的 STUFF 函数，您可以将第一个字符（前导逗号）替换为空字符串，如下所示：

stuff(
    (select ',' + Column 
     from Table
         inner where inner.Id = outer.Id 
     for xml path('')
), 1,1,'') as Values

Answer 4

我不相信有一种方法可以在一个查询中做到这一点，但是您可以使用临时变量来玩这样的技巧：

declare @s varchar(max)
set @s = ''
select @s = @s + City + ',' from Locations

select @s

它肯定比遍历光标更少的代码，而且可能更有效。

Answer 5

在单个 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

Answer 6

在 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   [email protected]
2   NULL
3   [email protected], [email protected]

这样即使您的数据包含无效的 XML 字符，

'"},{"":"' 也是安全的，因为如果您的数据包含 '"},{"":"'，它将被转义为"},{\"_\":\"

您可以将 ', ' 替换为任何字符串分隔

---

符 在 SQL Server 2017、Azure SQL 数据库中

您可以使用新的 STRING_AGG 函数

Answer 7

以下代码适用于 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

Answer 8

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]

Answer 9

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

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')

Answer 10

版本注意：对于此解决方案，您必须使用 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 错误，指出“缓冲区不足”。

Answer 11

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

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

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 

Answer 12

尝试这个查询

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

Answer 13

如果您运行的是 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());
    }
}