Unable to figure out how to join tables due to comma separated values

Question

I have a table of the following structure.

select loginid,alloted_area from tbllogin

Which returns this result.

loginid       alloted_area
------------- ---------------------------
01900017      22,153,169,174,179,301
01900117      254,91,92,285,286,287
01900217      2,690,326,327,336
17900501      null
17900601      28,513,409,410
17901101      254,91,92,285
17901701      59,1302,1303
17902101      2,690,326,327
17902301      20,159,371,161
17902401      null

I have another table tblareawhose identifiers are stored in comma separated values ​​in the above tables when a scope is assigned to the user. I want to join these two tables and leave records similar to the last one that has not yet been assigned to the area. Now I have been told several times that storing data in comma-separated values ​​is bad practice (I suppose due to a problem that I ran into). I know that this structure was created by another developer in my company, and please do not help me, instead of downvoting. This is what I tried:

declare @csv varchar(max)='';
SELECT @CSV = COALESCE(@CSV + ', ', '') + case when alloted_area is null or alloted_area='' then '0' else alloted_area end from tbllogin;
select * from tblarea where id in (select 0 union select sID from splitstring(@CSV,','));

, , . .

tbllogin

 loginid       alloted_area
 ------------- ---------------------------
 a1            1,3,5
 a2            2,4
 a3            1,4
 a4            null

tblarea

 id            area_name
 ------------- ---------------------------
 1             v
 2             w
 3             x
 4             y
 5             z

 login_id            area_name
 ------------- ---------------------------
 a1             v
 a1             x
 a1             z
 a2             w
 a2             y
 a3             v
 a3             y 

Answer 1

Split CROSS APPLY,

DECLARE @tbllogin TABLE (LoginID CHAR(2) NOT NULL PRIMARY KEY, alloted_area VARCHAR(MAX));
INSERT @tblLogin (LoginID, alloted_area)
VALUES ('a1', '1,3,5'), ('a2', '2,4'),('a3', '1,4'), ('a4', NULL);

DECLARE @tblArea TABLE (ID INT NOT NULL PRIMARY KEY, Area_Name CHAR(1));
INSERT @tblArea (ID, Area_Name) 
VALUES (1, 'v'), (2, 'w'), (3, 'x'), (4, 'y'), (5, 'z');


SELECT Dt.LoginID,A.Area_Name FROm
(
SELECT LoginID,Split.a.value('.', 'VARCHAR(1000)') AS alloted_area
            FROM (
                SELECT LoginID,CAST('<S>' + REPLACE(alloted_area, ',', '</S><S>') + '</S>' AS XML) AS alloted_area
                FROM @tbllogin
                ) AS A
            CROSS APPLY alloted_area.nodes('/S') AS Split(a)

)DT
Inner join 
 @tblArea A
 on A.ID=DT.alloted_area

LoginID     Area_Name
--------------------
a1          v
a1          x
a1          z
a2          w
a2          y
a3          v
a3          y

Answer 2

, LIKE, . CONCAT(',', alloted_area, ',') LIKE CONCAT('%,', ID, ',%')

,

-- SAMPLE DATA
DECLARE @tbllogin TABLE (LoginID CHAR(2) NOT NULL PRIMARY KEY, alloted_area VARCHAR(MAX));
INSERT @tblLogin (LoginID, alloted_area)
VALUES ('a1', '1,3,5'), ('a2', '2,4'),('a3', '1,4'), ('a4', NULL);
DECLARE @tblArea TABLE (ID INT NOT NULL PRIMARY KEY, Area_Name CHAR(1));
INSERT @tblArea (ID, Area_Name) 
VALUES (1, 'v'), (2, 'w'), (3, 'x'), (4, 'y'), (5, 'z');

-- QUERY
SELECT  l.LoginID,
        a.Area_Name
FROM    @tblLogin AS l
        INNER JOIN @tblArea AS a
            ON CONCAT(',', l.alloted_area, ',') LIKE CONCAT('%,', a.ID, ',%')
ORDER BY l.LoginID;

OUTPUT

LoginID     Area_Name
--------------------
a1          v
a1          x
a1          z
a2          w
a2          y
a3          v
a3          y

, allocated_area , - Aaron , LIKE .

, , , , , , . , .

, tblLoginArea:

LoginID     AreaID
------------------
a1          1
a1          3
a1          5
a2          2
a2          4
....etc

, - csv, :

CREATE VIEW dbo.LoginAreaCSV
AS
SELECT  l.LoginID,
        Allocated_Area = STUFF(la.AllocatedAreas.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    tblLogin AS l
        OUTER APPLY
        (   SELECT  CONCAT(',', la.AreaID)
            FROM    tblLoginArea AS la
            WHERE   la.LoginID = l.LoginID
            ORDER BY la.AreaID
            FOR XML PATH(''), TYPE
        ) AS la (AllocatedAreas);

, :

SELECT  l.LoginID, a.Area_Name
FROM    tblLogin AS l
        INNER JOIN tblLoginArea AS la
            ON la.LoginID = l.LoginID
        INNER JOIN tblArea AS a
            ON a.ID = la.AreaID;

DB

Answer 3

:

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
      ) AS y
    );

:

SELECT 
    t.loginid,tblarea.area_name 
FROM 
    tbllogin AS t
CROSS APPLY(SELECT value FROM SplitString(t.alloted_area,',')) as split
JOIN tblarea ON tblarea.id=split.Value