Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Обратите внимание, что Matt's code приведет к появлению 9X_select-statement дополнительной запятой в конце строки; использование 9X_select-statement COALESCE (или ISNULL в этом отношении), как 9X_sql-syntax показано в ссылке в сообщении Ланса, использует 9X_sql-statement аналогичный метод, но не оставляет вам лишнюю 9X_sql-srever запятую для удаления. Для полноты картины 9X_mssql вот соответствующий код из ссылки Лэнса 9X_sql-srever на sqlteam.com:

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

Я не верю, что это можно сделать в одном 9X_ms-sql-server запросе, но вы можете проделывать подобные 9X_sql-syntax трюки с временной переменной:

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

select @s

Это определенно 9X_sql-syntax меньше кода, чем обход курсора, и, вероятно, более 9X_sql-query эффективно.

В одном запросе SQL без использования предложения 9X_sqlselect FOR XML.
Общее табличное выражение используется 9X_sql-statement для рекурсивного объединения результатов.

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

Из того, что я вижу, FOR XML (как было опубликовано 9X_sql ранее) - это единственный способ сделать 9X_sql-select это, если вы хотите также выбрать другие 9X_sql-statement столбцы (что, как я полагаю, большинство), как 9X_sql-statement это делает OP. Использование COALESCE(@var... не позволяет 9X_mssql включать другие столбцы.

Обновление: Благодаря 9X_sql-srever programmingsolutions.net есть способ удалить "завершающую" запятую 9X_ms-sql-server до. Превратив его в начальную запятую и 9X_sql-statement используя функцию MSSQL STUFF, вы можете заменить 9X_sql-query первый символ (начальную запятую) пустой 9X_select-statement строкой, как показано ниже:

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 syntax

т.е.

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

Это 9X_sql-query будет работать, даже если ваши данные содержат 9X_string-concatenation недопустимые символы XML

'"}, {" ":"' безопасно, потому что если ваши данные содержат '"}, {" ":"', они 9X_sql-syntax будут заменены на "}, {\" _ \ ": \"

Вы 9X_sql-statement можете заменить "," любым разделителем 9X_sql-srever строк

А в SQL Server 2017 - База данных SQL Azure

Вы можете использовать новый STRING_AGG function

Приведенный ниже код будет работать для 9X_select-statement 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

Я нашел решение, создав следующую функцию:

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

9X_sql-srever

Ответ Мана не сработал для меня, поэтому 9X_sql-statement я внес некоторые изменения в этот ответ, чтобы 9X_sql-statement заставить его работать. Надеюсь, это кому-то 9X_sql-select поможет. Использование 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]

ПРИМЕЧАНИЕ К ВЕРСИИ. Вы должны использовать SQL Server 2005 или выше с уровнем совместимости 90 или выше для этого решения.

См. этот MSDN article для первого примера создания определяемой 9X_sqlselect пользователем агрегатной функции, которая 9X_select-statement объединяет набор строковых значений, взятых 9X_sql-select из столбца в таблице.

Моя скромная рекомендация 9X_select-statement - опустить добавленную запятую, чтобы вы 9X_sql могли использовать свой собственный специальный 9X_mssql разделитель, если таковой имеется.

Ссылаясь 9X_string-concatenation на версию примера 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();

Таким образом, когда 9X_sql-srever вы используете свой собственный агрегат, вы 9X_sql-server можете выбрать использование собственного 9X_sql-select разделителя или его отсутствие, например:

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

ПРИМЕЧАНИЕ. Будьте 9X_sql-statement осторожны с объемом данных, которые вы пытаетесь 9X_sql-select обработать в совокупности. Если вы попытаетесь 9X_sql-syntax объединить тысячи строк или много очень 9X_sql больших типов данных, вы можете получить 9X_sql-statement ошибку .NET Framework с сообщением «[t] буфера 9X_ms-sql-server недостаточно».

Что касается других ответов, человек, читающий 9X_sql-server ответ, должен знать таблицу транспортных 9X_sql-srever средств и создать таблицу транспортных средств 9X_ms-sql-server и данные для проверки решения.

Ниже приведен 9X_ms-sql-server пример, в котором используется таблица SQL 9X_sqlselect Server «Information_Schema.Columns». При 9X_sql-syntax использовании этого решения не нужно создавать 9X_ms-sql-server таблицы или добавлять данные. В этом примере 9X_ms-sql-server создается список имен столбцов, разделенных 9X_ms-sql-server запятыми, для всех таблиц в базе данных.

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 

Если вы используете SQL Server 2005, вы 9X_string-concatenation можете написать custom CLR aggregate function, чтобы справиться с этим.

Версия 9X_mssql 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());
    }
}

Попробуйте этот запрос

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('')
                    ),    
                    '',', '
                 ),
             '',''
            ),
        '', ''
        ) AS LocationList
    FROM Locations l
) ll ON ll.VehicleId = v.VehicleId

9X_select-statement