with Como criar uma função do SQL Server para “unir” várias linhas de uma subconsulta em um único campo delimitado?




w3schools concat sql (11)

Esta questão já tem uma resposta aqui:

Para ilustrar, suponha que eu tenha duas tabelas da seguinte maneira:

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

Eu quero escrever uma consulta para retornar os seguintes resultados:

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

Eu sei que isso pode ser feito usando cursores do lado do servidor, ou seja:

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

No entanto, como você pode ver, isso requer uma grande quantidade de código. O que eu gostaria é uma função genérica que me permita fazer algo assim:

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

Isso é possível? Ou algo semelhante?


Com as outras respostas, a pessoa que está lendo a resposta deve estar ciente da mesa do veículo e criar a tabela e os dados do veículo para testar uma solução.

Abaixo está um exemplo que usa a tabela "Information_Schema.Columns" do SQL Server. Usando essa solução, nenhuma tabela precisa ser criada ou dados adicionados. Este exemplo cria uma lista separada por vírgulas de nomes de colunas para todas as tabelas no banco de dados.

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 

A resposta de Mun não funcionou para mim, então fiz algumas alterações nessa resposta para que funcionasse. Espero que isso ajude alguém. Usando o 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]

Eu encontrei uma solução criando a seguinte função:

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

Uso:

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

O código abaixo funcionará para o 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

No SQL Server 2005

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

No SQL Server 2016

você pode usar a sintaxe FOR JSON

ou seja

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

E o resultado se tornará

Id  Emails
1   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

Isso funcionará até que seus dados contenham caracteres XML inválidos

o '"}, {" ":"' é seguro porque se os dados contiverem '"}, {" ":"', ele será salvo para "}, {\" _ \ ": \"

Você pode substituir ',' por qualquer separador de string

E no SQL Server 2017, Banco de Dados SQL do Azure

Você pode usar a nova função STRING_AGG


Note que o código de Matt irá resultar em uma vírgula extra no final da string; usar COALESCE (ou ISNULL) como mostrado no link na postagem de Lance usa um método semelhante, mas não deixa uma vírgula extra para ser removida. Por questões de integridade, aqui está o código relevante do link de Lance em sqlteam.com:

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

Em uma única consulta SQL, sem usar a cláusula FOR XML.
Uma Expressão de Tabela Comum é usada para concatenar recursivamente os resultados.

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

Eu não acredito que há uma maneira de fazer isso dentro de uma consulta, mas você pode jogar truques como este com uma variável temporária:

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

select @s

É definitivamente menos código do que andar sobre um cursor e, provavelmente, mais eficiente.


Se você estiver executando o SQL Server 2005, poderá gravar uma função agregada de CLR personalizada para lidar com isso.

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

Se você estiver usando o SQL Server 2005, poderá usar o comando 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]

É muito mais fácil do que usar um cursor e parece funcionar razoavelmente bem.


Do que eu posso ver FOR XML (como postado anteriormente) é a única maneira de fazê-lo se você quiser também selecionar outras colunas (o que eu acho que a maioria faria) como o OP faz. Usando COALESCE(@var... não permite a inclusão de outras colunas.

Atualização: Graças a programmingsolutions.net existe uma maneira de remover a vírgula "à direita" para. Ao transformá-lo em uma vírgula principal e usar a função STUFF do MSSQL, você pode substituir o primeiro caractere (vírgula principal) por uma string vazia, conforme abaixo:

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






string-concatenation