如何创建一个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

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

当前回答

妈妈的答案对我没用，所以我对答案做了一些修改，让它起作用。希望这能帮助到一些人。使用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]

2016-10-13 00:07:03

其他回答

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

2010-09-09 00:15:49

妈妈的答案对我没用，所以我对答案做了一些修改，让它起作用。希望这能帮助到一些人。使用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]

2016-10-13 00:07:03

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

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

我的建议是去掉附加的逗号，这样您就可以使用自己的特殊分隔符(如果有的话)。

参考示例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();

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

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

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

2010-02-09 04:45:48

试试这个查询

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

2015-09-04 06:10:54

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

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

select @s

这绝对比在游标上移动要少的代码，而且可能更有效率。

2008-08-10 00:12:25

如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?

推荐文章

最新文章

标签