Datafly.pro

Transformer une liste avec séparateurs en colonne

Pour expérimenter, créons une petite liste de courses pour chaque jour :
 

CREATE TABLE MesCourses (
ID INT IDENTITY PRIMARY KEY,
DateCourses DATETIME2(2) DEFAULT (GETDATE()),
ListeCourses VARCHAR(MAX))

INSERT MesCourses (DateCourses,ListeCourses)
VALUES (GETDATE()-1,'Pain, Vin, Camembert, Yaourt')
INSERT MesCourses (DateCourses,ListeCourses)
VALUES (GETDATE(),'Brioche, Champagne, Caviar, Foie Gras')

Avant SQL Server 2016, la meilleure solution était d'écrire sa propre udf et d'y utiliser la récursivité. Par exemple : 

CREATE FUNCTION fnSplitString(@str varchar(max),@sep varchar(1))
RETURNS TABLE
AS
RETURN
    WITH a AS(
        SELECT CAST(0 AS BIGINT) as pos1,CHARINDEX(@sep,@str) pos2
        UNION ALL
        SELECT pos2+1,CHARINDEX(@sep,@str,pos2+1)
        FROM a
        WHERE pos2>0
    )
    SELECT SUBSTRING(@str,pos1,COALESCE(NULLIF(pos2,0),LEN(@str)+1)-pos1) as value
    FROM a
-- Transformer en colonnes

SELECT DateCourses, LTRIM(value) AS Achat
FROM MesCourses
CROSS APPLY fnSplitString(ListeCourses, ',')

Depuis SQL Server 2016, la fonction existe déjà, plus besoin de la créer : STRING_SPLIT. La requête pourra donc s'écrire :

SELECT DateCourses, LTRIM(value) AS Achat
FROM MesCourses
CROSS APPLY STRING_SPLIT(ListeCourses, ',')

Notons que le plan d'exécution est totalement différent : avec un nombre de lignes conséquent dans la table, il n'y a pas photo sur les performances : 27 secondes avec l'udf, 7 secondes avec STRING_SPLIT...

Produire une liste avec séparateurs à partir d'une colonne

Prenons ici un autre exemple, celui-ci est particulièrement courant : produire une liste de colonnes pour chaque table.

Avant SQL Server 2017, la méthode pour produire une liste était généralement d'utiliser une sous-requête for XML PATH. Comme ceci :

select o.object_id,o.name,
 STUFF(  -- suppression du premier séparateur
  (
  SELECT ', ' + name as [text()] FROM    sys.columns c
  WHERE c.object_id = o.object_id
  ORDER BY c.column_id               
  FOR XML PATH('') -- sous-requête en xml
  )
 ,1,2,'')                                                
 as ColumnList
from sys.objects o
WHERE o.is_ms_shipped = 0 AND type = 'U'
ORDER BY o.name

Depuis SQL Server 2017, c'est considérablement plus simple, car nous avons la fonction STRING_AGG qui fait le boulot.

select o.object_id,o.name
,STRING_AGG(c.name,', ') WITHIN GROUP (ORDER BY c.column_id) AS ColumnList
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.is_ms_shipped = 0
GROUP BY o.object_id,o.name
ORDER BY o.name

Côté performances, les deux solutions semblent beaucoup plus proches : pourquoi pas tester les deux ?

Bonne manipulation de listes !