如何创建SQL Server函数，将子查询中的多个行“连接”到单个分隔字段?(复制)

This question already has an answer here:

这个问题已经有了答案:

• How to concatenate text from multiple rows into a single text string in SQL server? 43 answers
• 如何将多个行中的文本连接到SQL服务器中的单个文本字符串?43个答案

To illustrate, assume that I have two tables as follows:

举例来说，假设我有以下两张表:

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

I want to write a query to return the following results:

我想写一个查询返回以下结果:

VehicleID Name    Locations
1         Chuck   New York, Seattle, Vancouver
2         Larry   Los Angeles, Houston

I know that this can be done using server side cursors, ie:

我知道这可以通过服务器端游标来实现，即:

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

However, as you can see, this requires a great deal of code. What I would like is a generic function that would allow me to do something like this:

但是，正如您所看到的，这需要大量的代码。我想要的是一个通用函数可以让我做这样的事情:

SELECT VehicleID
     , Name
     , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles

Is this possible? Or something similar?

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

13 个解决方案

#1

If you're using SQL Server 2005, you could use the FOR XML PATH command.

如果使用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]

It's a lot easier than using a cursor, and seems to work fairly well.

它比使用游标要容易得多，而且似乎运行得相当好。

#2

Note that Matt's code will result in an extra comma at the end of the string; using COALESCE (or ISNULL for that matter) as shown in the link in Lance's post uses a similar method but doesn't leave you with an extra comma to remove. For the sake of completeness, here's the relevant code from Lance's link on sqlteam.com:

注意Matt的代码会在字符串的末尾产生一个额外的逗号;在Lance's post的链接中使用COALESCE(或ISNULL)使用类似的方法，但并没有留下一个额外的逗号来删除。为了完整起见，以下是兰斯在sqlteam.com上链接的相关代码:

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

#3

I don't belive there's a way to do it within one query, but you can play tricks like this with a temporary variable:

我不相信有一种方法可以在一个查询中完成，但是你可以用一个临时变量来做如下操作:

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

select @s

It's definitely less code than walking over a cursor, and probably more efficient.

它的代码肯定比鼠标指针要少，而且可能更高效。

#4

In a single SQL query, without using the FOR XML clause.
A Common Table Expression is used to recursively concatenate the results.

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

#5

From what I can see FOR XML (as posted earlier) is the only way to do it if you want to also select other columns (which I'd guess most would) as the OP does. Using COALESCE(@var... does not allow inclusion of other columns.

从我所看到的XML(如前所述)中，如果您还想像OP那样选择其他列(我猜大多数都是这样)，那么惟一的方法就是这样做。使用合并(@var……不允许包含其他列。

Update: Thanks to programmingsolutions.net there is a way to remove the "trailing" comma to. By making it into a leading comma and using the STUFF function of MSSQL you can replace the first character (leading comma) with an empty string as below:

更新:感谢编程解决方案。net，有一种方法可以删除“结尾”逗号。通过将其转换为一个主要的逗号，并使用MSSQL的STUFF函数，您可以将第一个字符(主要的逗号)替换为空字符串，如下所示:

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

#6

In SQL Server 2005

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

---

In SQL Server 2016

you can use the FOR JSON syntax

可以使用FOR JSON语法

i.e.

即。

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

And the result will become

结果会变成

Id  Emails
1   [e-mail]
2   NULL
3   [e-mail], [e-mail]

This will work even your data contains invalid XML characters

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

the '"},{"":"' is safe because if you data contain '"},{"":"', it will be escaped to "},{\"_\":\"

" },{ ":“是安全的,因为如果你资料包含”},{”“:”,它会逃到”},{ \“_ \”,\“

You can replace ', ' with any string separator

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

---

And in SQL Server 2017, Azure SQL Database

You can use the new STRING_AGG function

您可以使用新的STRING_AGG函数

#7

The below code will work for Sql Server 2000/2005/2008

下面的代码将适用于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

#8

I've found a solution by creating the following function:

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

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

Usage:

用法:

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

#9

VERSION NOTE: You must be using SQL Server 2005 or greater with Compatibility Level set to 90 or greater for this solution.

版本说明:您必须使用SQL Server 2005或更高版本，并将此解决方案的兼容性级别设置为90或更高。

See this MSDN article for the first example of creating a user-defined aggregate function that concatenates a set of string values taken from a column in a table.

请参阅这篇MSDN文章，了解创建用户定义聚合函数的第一个示例，该聚合函数连接从表中的列获取的一组字符串值。

My humble recommendation would be to leave out the appended comma so you can use your own ad-hoc delimiter, if any.

我的拙见是省略了附加的逗号，这样您就可以使用自己的专用分隔符(如果有的话)。

Referring to the C# version of Example 1:

参考示例1的c#版本:

change:  this.intermediateResult.Append(value.Value).Append(',');
    to:  this.intermediateResult.Append(value.Value);

And

和

change:  output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
    to:  output = this.intermediateResult.ToString();

That way when you use your custom aggregate, you can opt to use your own delimiter, or none at all, such as:

这样，当您使用自定义聚合时，您可以选择使用自己的分隔符，或者根本不使用，例如:

SELECT dbo.CONCATENATE(column1 + '|') from table1

NOTE: Be careful about the amount of the data you attempt to process in your aggregate. If you try to concatenate thousands of rows or many very large datatypes you may get a .NET Framework error stating "[t]he buffer is insufficient."

注意:要注意您试图在聚合中处理的数据量。如果您尝试连接数千行或许多非常大的数据类型，您可能会得到一个. net框架错误，说明“[t]缓冲区不够”。

#10

With the other answers, the person reading the answer must be aware of the vehicle table and create the vehicle table and data to test a solution.

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

Below is an example that uses SQL Server "Information_Schema.Columns" table. By using this solution, no tables need to be created or data added. This example creates a comma separated list of column names for all tables in the database.

下面是一个使用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 

#11

Mun's answer didn't work for me so I made some changes to that answer to get it to work. Hope this helps someone. Using SQL Server 2012:

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]

#12

Try this query

试试这个查询

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

#13

If you're running SQL Server 2005, you can write a custom CLR aggregate function to handle this.

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

C# version:

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());
    }
}