196 votos

Hay una manera de crear una función de SQL Server para "unirse" a varias filas de una subconsulta en una sola delimitado campo?

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

Yo sé que esto puede ser hecho usando los cursores de 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 se puede ver, esto requiere de una gran cantidad de código. Lo que me gustaría es una función genérica que se me permitiera hacer algo como esto:

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

Es esto posible? O algo similar?

262voto

Mun Puntos 6844

Si está utilizando SQL Server 2005, puede utilizar el FOR XML PATH comando.

SELECT [VehicleID]
     , [Name]
     , (SELECT CAST(City + ', ' AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR XML PATH ('')
      ) AS Locations
FROM [Vehicle]

Es mucho más fácil que el uso de un cursor, y parece que funciona bastante bien.

88voto

Mike Powell Puntos 2913

Tenga en cuenta que Matt código anterior dará como resultado una coma extra al final de la cadena; el uso de FUSIONARSE (o ISNULL, para el caso), como se muestra en el enlace de Lanza del post, utiliza un método similar, pero no te deja con una coma extra a quitar. En aras de la exhaustividad, aquí está el código correspondiente de Lanza del enlace en sqlteam.com:

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

47voto

Matt Hamilton Puntos 98268

No creo que hay una forma de hacerlo dentro de una consulta, pero se puede jugar trucos como este, con una variable temporal:

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

select @s

Es, definitivamente, menos código que caminar más de un cursor, y probablemente más eficiente.

24voto

ZunTzu Puntos 196

En una sola consulta SQL, sin el uso de la cláusula FOR XML.
Una Expresión de Tabla Común se utiliza de forma recursiva concatenar 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

23voto

John B Puntos 459

Por lo que puedo ver FOR XML (ya publicado anteriormente) es la única manera de hacerlo si desea seleccionar también otras columnas (que me imagino que la mayoría diría) como el OP. Utilizando COALESCE(@var... no permite la inclusión de otras columnas.

Actualización: Gracias programmingsolutions.net hay una forma para eliminar el "final" de coma. Por lo que lo convierte en un líder de la coma y el uso de la STUFF función de MSSQL puede reemplazar el primer carácter (líder de la coma) con una cadena vacía como a continuación:

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

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X