如何创建一个SQL Server函数，将子查询中的多行“连接”为一个带有分隔符的字段？

Question

如何创建一个SQL Server函数，将子查询中的多行“连接”为一个带有分隔符的字段？

214

举例说明，假设我有以下两个表：

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

这是否可行？或类似的事情？

- Templar

2

一个类似的答案，但是回答更加完整。https://dev59.com/sWMm5IYBdhLWcg3wdu1V#17591536 - Narkha

@Narkha 太棒了！我在搜索中没有看到这个。我现在会删除我的问题。谢谢！ - SkyeBoniwell

13个回答

89

注意，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

- Mike Powell

7

在我看来，没有额外的逗号很不错，而且比被接受的解决方案更易于阅读和理解。非常感谢！ - Beska

4

这不是一个可靠的解决方案。 - Lukáš Lánský

4

只要你不在意顺序，它是可靠的。 - codeulike

1

即使您不关心顺序，它也可能会跳过结果中的数据。 - Der_Meister

1

为了可靠的排序，添加ORDER BY？ - Pete Alvin

49

我不相信有一种方法可以在一个查询中完成它，但你可以使用像这样的临时变量来达到目的：

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

select @s

这肯定比遍历光标的代码量要少，而且可能更高效。

- Matt Hamilton

12

我相当确定你可以在最后一行删掉“probably”。 - Marc Gravell

1

“我不相信有一种方法可以在一个查询中完成。” 是的，有的。SQL Server 2005具有FOR XML和CTE。 - T.J. Crowder

2

它不可靠，取决于执行计划，可能会丢失行。请参见KB。 - Der_Meister

2

这种技术或特性叫什么？当进行 SELECT @s = @s 变量赋值时，包括其现有值，并且对结果集中的每一行都进行了相同的操作。 - Baodad

很好的答案，但请注意这个解决方案似乎没有记录，因此微软有可能在未经警告的情况下删除这个功能。 - Pouria Moosavi

我们的一个自动化测试现在失败了，可能是因为不再支持这个解决方案。我看到 String_Agg 是首选。 - gdbj

25

使用单个SQL查询，在不使用FOR XML子句的情况下。
使用公共表达式（CTE）来递归连接结果。

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

- ZunTzu

4

谢谢你。这是解决此问题的少数几个方案之一，它不使用变量、函数、FOR XML子句或CLR代码。这意味着我能够根据您的解决方案来解决TSQL初学者挑战4-连接来自多行的值。 - Iain Samuel McLean Elder

1

谢谢！我需要将一系列作为单独布尔短语行表达的SQL代码片段转换为单个复杂代码表达式，并且很高兴尝试您的方法。 - Paul Chernoch

2

这个方案是否比其他解决方案具有更好的性能优势？ - PeonProgrammer

3

不，对于大结果集它的运行效果非常差，并且很可能会出现错误：“语句完成之前已耗尽最大递归次数100。”你可以通过在结尾处指定“OPTION(MAXRECURSION 0)”来解决这个问题，但是这样做可能导致查询需要非常长的时间才能执行完。 - Kirk Woll

23

据我所见，如果你还想选择其他列的话（我猜大多数人都会这么做），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

- John B

23

在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函数

- teamchong

1

我去掉了这部分代码：TYPE ).value('text()[1]', 'nvarchar(max)')，但它仍然很好用，不确定它本来的作用是什么。 - Adam Nofsinger

5

应该解码XML，如果[City]中有像& < >这样的字符，输出将变成& < >，如果你确定[City]没有这些特殊字符，那么安全地删除它。- Steven Chong - teamchong

2

+1。这个回答被低估了。您应该编辑它以说明这是仅有的一个不会转义特殊字符（如&，<，>等）的答案之一。此外，如果我们使用.value（'。'，'nvarchar（max）'），结果不会相同吗？ - Baodad

1

嗨Baodad，结果是一样的，但是我测试了一下，使用'text()[1]'而不是'.'时性能更好，虽然差别不大。 - teamchong

13

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

- Binoj Antony

1

那个VARCHAR（1000），那是某种限制，对吧？因为当我在列列表上运行类似的连接查询时，它会在大约950个字符左右停止，无论指定的大小如何。 - John Leidegren

6

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

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

- Gil

1

这非常类似于Mike Powell和Binoj Antony的答案。 - Andriy M

2

很棒的解决方案，因为可读性优于其他答案 +1 - PeonProgrammer

3

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]

- nurseybushc

2

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

以下是一个示例，使用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

- Mike Barlow - BarDev

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mun · Accepted Answer

如果您正在使用 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 2017以来可用的STRING_AGG方法来更轻松、更高效地完成。

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

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