Existe-t-il un moyen de créer une fonction SQL Server pour "joindre" plusieurs lignes d'une sous-requête dans un seul champ délimité ?

Question

Pour illustrer, supposons que j'ai deux tables comme suit :

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

Je veux écrire une requête qui renvoie les résultats suivants :

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

Je sais que cela peut être fait en utilisant des curseurs côté serveur, par exemple :

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

Cependant, comme vous pouvez le constater, cela nécessite une grande quantité de code. Ce que j'aimerais, c'est une fonction générique qui me permettrait de faire quelque chose comme ceci :

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

Est-ce possible ? Ou quelque chose de similaire ?

Answer 1

Si vous utilisez SQL Server 2005, vous pouvez utiliser la commande FOR XML PATH.

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

C'est beaucoup plus facile que d'utiliser un curseur, et cela semble fonctionner assez bien.

Answer 2

Notez que le code de Matt ci-dessus entraînera une virgule supplémentaire à la fin de la chaîne de caractères ; l'utilisation de COALESCE (ou ISNULL d'ailleurs) comme indiqué dans le lien du message de Lance utilise une méthode similaire mais ne vous laisse pas avec une virgule supplémentaire à supprimer. Pour être complet, voici le code correspondant au lien de Lance sur sqlteam.com :

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

Answer 3

Je ne pense pas qu'il y ait un moyen de le faire dans une seule requête, mais vous pouvez jouer des tours comme celui-ci avec une variable temporaire :

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

select @s

C'est certainement moins de code que de passer sur un curseur, et probablement plus efficace.

Answer 4

Dans une seule requête SQL, sans utiliser la clause FOR XML.
Une expression de table commune est utilisée pour concaténer récursivement les résultats.

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

De ce que je peux voir FOR XML (comme indiqué précédemment) est la seule façon de procéder si vous souhaitez également sélectionner d'autres colonnes (ce qui est le cas de la plupart des gens) comme le fait le PO. Utilisation de COALESCE(@var... ne permet pas d'inclure d'autres colonnes.

Mise à jour : Merci à programmingsolutions.net il existe un moyen de supprimer la virgule de fin. En la transformant en une virgule de tête et en utilisant la fonction STUFF de MSSQL, vous pouvez remplacer le premier caractère (virgule de tête) par une chaîne vide comme ci-dessous :

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