Joindre une table avec des valeurs varchar séparées par des virgules avec les valeurs d'ID d'une autre table dans SQL Server

Tout d'abord, vous devez repenser vos tableaux. Il y a besoin d'une table de jonction, qui contiendra quel utilisateur est connecté à quel fruit. C'est une relation N: N .

Vous devez donc créer une telle table:

FruitUser

select * from users u
join FruitUser fu on u.id = fu.userid
join Fruit f on f.id = fu.fruitId

UserId est FK vers la table Users , FruitId est FK vers la table Fruits et ces deux colonnes forment une clé primaire compoiste . C'est une approche standard.

Ensuite, vous pouvez utiliser une jointure simple pour obtenir des résultats:

UserId  FruitId
1       1
1       3
2       3

Pour SQL Server 2014 où vous ne pouvez pas utiliser STRING_SPLIT , vous pouvez diviser le varchar en utilisant XML comme suit.

  ;WITH cte 
     AS (SELECT id, 
                name, 
                fruitid 
         FROM   (SELECT *, 
                        Cast('<X>' + Replace(F.fruit, ',', '</X><X>') 
                             + '</X>' AS XML) AS xmlfilter 
                 FROM   users F)F1 
                CROSS apply (SELECT fdata.d.value('.', 'varchar(50)') AS FruitId 
                             FROM   f1.xmlfilter.nodes('X') AS fdata(d)) O) 
SELECT * 
FROM   cte C 
       INNER JOIN fruit F 
               ON F.id = Cast(C.fruitid AS INT) 

Exemple de données

Userid  UserName    FruitId   FruitName
------------------------------------------
1        Bob            1       Apple
1        Bob            3       Grape
2        Jack           3       Grape

Script Sql

;WITH CTE
AS
(
SELECT  UserId,
        UserName ,      
        CAST(Split.a.value('.', 'nvarchar(1000)') AS INT) AS FruitId
FROM
(       SELECT  u.ID AS UserId,
                u.Name AS UserName ,      
                CAST( '<S>'+ REPLACE(fruit,',','</S><S>')+'</S>' AS XML) AS FruitId             
        FROM @Fruit f
        INNER JOIN  @Users u
        ON u.ID=f.ID 
)AS A
CROSS APPLY FruitId.nodes('S') AS Split(a)
)
SELECT Userid,
       UserName,
       FruitId,
       ft.name AS FruitName
FROM CTE c
LEFT JOIN (SELECT * FROM @Fruit) AS Ft
ON ft.ID=c.FruitId

Résultat

DECLARE @Users AS TABLE(ID INt,  Name  VARCHAR(100),fruit VARCHAR(100))
INSERT INTO @Users
SELECT 1,'Bob' ,'1,3' UNION ALL
SELECT 2,'Jack','3'

DECLARE  @Fruit AS TABLE(ID INt,  Name  VARCHAR(100))
INSERT INTO @Fruit
SELECT 1,'Apple' UNION ALL
SELECT 2,'Orange'UNION ALL
SELECT 3,'Grape'

Vous pouvez obtenir le résultat souhaité sans rien changer avec cette requête

select * from
(SELECT ID, [name],LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS fruitid
    FROM
(SELECT ID,[name],CAST('<XMLRoot><RowData>' + REPLACE(fruit,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x FROM  {User table})t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)) u 
inner join {fruit table} f on f.id = u.fruitid

Puisque vous avez SQL Server 2014, vous avez diverses options telles que CLR, XML et les fonctions numériques. Le meilleur est CLR mais c'est complexe. Vous pouvez donc utiliser ce code XML.

SELECT u.Id, u.Name, f.name  
FROM Users u
inner join Fruit f  on f.ID IN (SELECT cast(value as int)FROM STRING_SPLIT(u.fruit, ','));

Création de tableaux physiques avec des exemples de données

ID  Name    FruitNames
---------------------
1   Bob     Apple,Grape
2   Jack    Grape

Créez une fonction table pour récupérer les noms des fruits séparés par des virgules

SELECT ID
      ,Name
      ,uf.Reqdata AS FruitNames
FROM TempUsers u
CROSS APPLY [dbo].[udf_GetFruitNames](u.Fruit) AS uf

Or

SELECT ID
  ,Name
  ,(SELECT Reqdata FROM [dbo].[udf_GetFruitNames](u.Fruit) ) AS FruitNames
  FROM TempUsers u

Requête SQL

CREATE FUNCTION [dbo].[udf_GetFruitNames]
(
@vc_String nvarchar(max)
)
RETURNS  @OutTable TABLE
(
Reqdata nvarchar(max)
)
AS
BEGIN

    DECLARE @Temp AS TABLE
    (
        DATA nvarchar(max)
    )
    INSERT INTO @Temp
    SELECT @vc_String;

    DECLARE @Temp1 AS TABLE
    (
    DATA nvarchar(max)
    )
    INSERT INTO @Temp1
    SELECT 
            STUFF((SELECT DISTINCT ','+ Name FROM
            (
                SELECT  ID,
                        Name    
                FROm TempFruit 
                WHERE ID IN  ( SELECT    
                                    CAST(Split.a.value('.', 'nvarchar(1000)') AS INT) AS FruitId
                                    FROM
                                    (   SELECT    
                                        CAST( '<S>'+ REPLACE(DATA,',','</S><S>')+'</S>' AS XML) AS FruitId             
                                        FROM @Temp f

                                    )AS A
                                    CROSS APPLY FruitId.nodes('S') AS Split(a))
            ) As dt FOR XML PATH ('')),1,1,'') As FruitName


        INSERT INTO @OutTable
        SELECT * FROM @Temp1
RETURN
END

Résultat

CREATE TABLE TempUsers 
( ID INT,
  Name  VARCHAR(100),
  Fruit VARCHAR(100)
 )
INSERT INTO TempUsers 
SELECT 1,'Bob' ,'1,3' UNION ALL
SELECT 2,'Jack','3'

CREATE TABLE TempFruit 
    ( ID INT,
      Name  VARCHAR(100))

INSERT INTO TempFruit
SELECT 1,'Apple' UNION ALL
SELECT 2,'Orange'UNION ALL
SELECT 3,'Grape'