Pregunta ¿Cómo crear una función de SQL Server para "unir" múltiples filas de una subconsulta en un único campo delimitado? [duplicar]


Esta pregunta ya tiene una respuesta aquí:

Para ilustrar, supongamos que tengo dos tablas de la siguiente manera:

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

Quiero escribir una consulta para devolver los siguientes resultados:

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

Sé que esto se puede hacer utilizando los cursores del lado del servidor, es decir:

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

Sin embargo, como puede ver, esto requiere una gran cantidad de código. Lo que me gustaría es una función genérica que me permita hacer algo como esto:

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

es posible? ¿O algo similar?


169
2017-08-09 20:11


origen


Respuestas:


Si está utilizando SQL Server 2005, puede usar el 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]

Es mucho más fácil que usar un cursor, y parece funcionar bastante bien.


231
2017-08-10 01:05



Tenga en cuenta que El código de Matt dará como resultado una coma adicional al final de la cadena; usar COALESCE (o ISNULL para ese asunto) como se muestra en el enlace en la publicación de Lance utiliza un método similar pero no te deja con una coma extra para eliminar. En aras de la exhaustividad, aquí está el código relevante del enlace de Lance en sqlteam.com:

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

80
2017-08-10 13:15



No creo que haya una forma de hacerlo en una consulta, pero puedes jugar trucos como este con una variable temporal:

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

select @s

Definitivamente es menos código que caminar sobre un cursor, y probablemente sea más eficiente.


41
2017-08-10 00:12



En una sola consulta SQL, sin usar la cláusula FOR XML.
Una Expresión de tabla común se utiliza para concatenar recursivamente los 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

21
2018-01-06 15:20



Por lo que puedo ver FOR XML (como se publicó anteriormente) es la única forma de hacerlo si también desea seleccionar otras columnas (lo que supongo que haría la mayoría) como lo hace OP. Utilizando COALESCE(@var... no permite la inclusión de otras columnas.

Actualizar: Gracias a programmingsolutions.net hay una forma de eliminar la coma "final". Al convertirlo en una coma líder y usar el STUFF función de MSSQL puede reemplazar el primer carácter (coma inicial) con una cadena vacía de la siguiente manera:

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

19
2018-06-23 02:08



En SQL Server 2005

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

En SQL Server 2016

puedes usar el PARA sintaxis JSON

es decir

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

Y el resultado será

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

Esto funcionará incluso si sus datos contienen caracteres XML no válidos

el '"},{"":" 'es seguro porque si sus datos contienen' "}, {"":" ', se escapará a "}, {\" _ \ ": \"

Puede reemplazar ',' con cualquier separador de cadenas


Y en SQL Server 2017, Azure SQL Database

Puedes usar el nuevo Función STRING_AGG


12
2017-09-09 00:15



El siguiente código funcionará para 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

9
2018-06-18 12:41



Encontré una solución creando la siguiente función:

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

5
2018-05-30 15:15



NOTA DE VERSIÓN: Debe utilizar SQL Server 2005 o superior con Nivel de compatibilidad establecido en 90 o superior para esta solución.

Mira esto Artículo de MSDN para el primer ejemplo de creación de una función de agregado definida por el usuario que concatena un conjunto de valores de cadena tomados de una columna en una tabla.

Mi humilde recomendación sería dejar de lado la coma adjunta para que pueda usar su propio delimitador ad-hoc, en su caso.

En referencia a la versión de C # del Ejemplo 1:

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

Y

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

De esta forma, cuando use su agregado personalizado, puede optar por usar su propio delimitador, o ninguno, como por ejemplo:

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

NOTA: Tenga cuidado con la cantidad de datos que intenta procesar en su agregado. Si intenta concatenar miles de filas o muchos tipos de datos muy grandes, puede obtener un error de .NET Framework indicando "[t] e el buffer es insuficiente".


1
2018-02-09 04:45



Preguntas populares