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

concatenar numeros en sql

Question

Esta pregunta ya tiene una respuesta aquí:

• ¿Cómo concatenar texto de múltiples filas en una sola cadena de texto en el servidor SQL? 43 respuestas

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?

Answer 1

Con las otras respuestas, la persona que lee la respuesta debe conocer la tabla del vehículo y crear la tabla y los datos del vehículo para probar una solución.

A continuación se muestra un ejemplo que utiliza la tabla "Information_Schema.Columns" de SQL Server. Al usar esta solución, no es necesario crear tablas ni agregar datos. Este ejemplo crea una lista de nombres de columna separados por comas para todas las tablas en la base de datos.

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 

Answer 2

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

Actualización: gracias a programmingsolutions.net hay una manera de eliminar la coma "final". Al convertirlo en una coma inicial y usar la función STUFF de MSSQL, puede reemplazar el primer carácter (coma inicial) con una cadena vacía como se muestra a continuación:

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

Answer 3

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

Answer 4

En SQL Server 2005+:

SELECT [VehicleID]
    , [Name]
    , [Locations] = Isnull( Stuff(
        ( SELECT N'', '' + [City] FROM [Locations]
        WHERE VehicleID = a.VehicleID
        FOR XML PATH(''''),TYPE ).value(''text()[1]'', ''nvarchar(max)'')
    , 1, 2, N''''), N'''')
FROM [Vehicle] a

Answer 5

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

Answer 6

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

Answer 7

La respuesta de Mun no funcionó para mí, así que hice algunos cambios en esa respuesta para que funcione. Espero que esto ayude a alguien. Usando 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]

Answer 8

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.

Answer 9

Prueba esta consulta

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

Answer 10

Si está ejecutando SQL Server 2005, puede escribir una función agregada CLR personalizada para manejar esto.

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

Answer 11

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.

Answer 12

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

Answer 13

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

Consulte este artículo de MSDN para obtener 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".