Comment joindre efficacement des données dans des colonnes délimitées par des virgules dans SQL ?

Joindre une colonne de données délimitées par des virgules

Les valeurs séparées par des virgules (CSV) sont couramment utilisées pour stocker plusieurs valeurs dans une seule colonne dans un base de données relationnelle. Cependant, ce format peut présenter des défis lors de l'exécution de tâches de manipulation de données. Cet article explore les techniques permettant de joindre des données dans des colonnes délimitées par des virgules.

Exemple de scénario

Considérez les deux tableaux suivants :

Tableau 1 (T1)

col1	col2
C1	john
C2	alex
C3	piers
C4	sara

Tableau 2 (T2)

col1	col2
R1	C1,C2,C4
R2	C3,C4
R3	C1,C4

Résultat souhaité :

col1	col2
R1	john,alex,sara
R2	piers,sara
R3	john,sara

Normalisation pour des performances optimales

Idéalement , les données doivent être normalisées, en éliminant les valeurs séparées par des virgules du tableau 2. En créant un nouveau tableau avec la structure suivante :

CREATE TABLE T2 (
    col1 varchar(2),
    col2 varchar(2),
    PRIMARY KEY (col1, col2),
    FOREIGN KEY (col2) REFERENCES T1 (col1)
);

Les données peuvent être insérées en conséquence, permettant des jointures efficaces :

INSERT INTO T2 (col1, col2) VALUES ('R1', 'C1'), ('R1', 'C2'), ('R1', 'C4'), ('R2', 'C3'), ('R2', 'C4'), ('R3', 'C1'), ('R3', 'C4');

Jointure par requête directe :

Utilisation du tables normalisées, une simple jointure permet de récupérer les données souhaitées :

SELECT t2.col1, t1.col2
FROM t2
INNER JOIN t1
ON t2.col2 = t1.col1;

Concaténation pour Sortie séparée par des virgules :

Si la sortie souhaitée nécessite des valeurs séparées par des virgules, les fonctions FOR XML PATH et STUFF peuvent être utilisées :

SELECT DISTINCT t2.col1, 
STUFF(
    (SELECT DISTINCT ', ' + t1.col2
    FROM t1
    INNER JOIN t2 t ON t1.col1 = t.col2
    WHERE t2.col1 = t.col1
    FOR XML PATH ('')), 1, 1, '') AS col2
FROM t2;

Fonction Split pour les valeurs non normalisées Données :

En l'absence de données normalisées, une fonction de fractionnement peut être créée pour diviser valeurs séparées par des virgules en lignes individuelles :

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
RETURNS @temptable TABLE (items varchar(MAX))       
AS       
BEGIN      
    DECLARE @idx int       
    DECLARE @slice varchar(8000)       

    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL RETURN       

    WHILE @idx != 0       
    BEGIN       
        SET @idx = CHARINDEX(@Delimiter, @String)       
        IF @idx != 0       
            SET @slice = LEFT(@String, @idx - 1)       
        ELSE       
            SET @slice = @String       

        IF(LEN(@slice) > 0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       

        SET @String = RIGHT(@String, LEN(@String) - @idx)       
        IF LEN(@String) = 0 BREAK       
    END   
RETURN 
END;

En utilisant la fonction de fractionnement au sein d'un CTE (Common Table Expression), les données peuvent être traitées :

WITH CTE AS
(
  SELECT c.col1, t1.col2
  FROM t1
  INNER JOIN 
  (
    SELECT t2.col1, i.items AS col2
    FROM t2
    CROSS APPLY dbo.Split(t2.col2, ',') i
  ) c
    ON t1.col1 = c.col2
) 
SELECT DISTINCT c.col1, 
STUFF(
    (SELECT DISTINCT ', ' + c1.col2
    FROM CTE c1
    WHERE c.col1 = c1.col1
    FOR XML PATH('')), 1, 1, '') AS col2
FROM CTE c

Alternative FOR Requête XML PATH :

Une autre approche implique l'application directe de FOR XML CHEMIN :

SELECT col1, 
(
  SELECT ', '+t1.col2
  FROM t1
  WHERE ','+t2.col2+',' LIKE '%,'+CAST(t1.col1 AS VARCHAR(10))+',%'
  FOR XML PATH(''), TYPE
).value('SUBSTRING(TEXT()[1], 3)', 'VARCHAR(MAX)') AS col2
FROM t2;

Conclusion

La jointure entre des données délimitées par des virgules nécessite un examen attentif des performances et du formatage de sortie. La normalisation offre des performances optimales, mais si cela n'est pas réalisable, les fonctions fractionnées ou les requêtes directes FOR XML PATH offrent des alternatives. Ces techniques permettent une manipulation et une récupération efficaces des données à partir de colonnes séparées par des virgules.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!