Can't 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 tblarea whose ids are stored in comma separated values in the above tables when an area is assigned to a user. I want to join these two tables and leave entries like the last one that has not yet been assigned an area. Now I have been told several times on that storing data in comma separated values is a bad practice(I suppose it's because of the problem that I am facing) I know that but this structure has been created by another developer at my company not me so please help instead of downvoting. This is what I have 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,','));

This does get the area but there is no way it can give me the login of users that the areas have been assigned to. Sample input and output.

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

After joining I need this result

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

Answer 1

By Using Split and CROSS APPLY we can achieve the desired Output

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

OutPut

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

Answer 2

You can join using LIKE, e.g. CONCAT(',', alloted_area, ',') LIKE CONCAT('%,', ID, ',%')

So for a full example

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

You could arguably split allocated_area into separate rows, but the article Split strings the right way – or the next best way by Aaron Bertrand shows that in these circumstances LIKE will outperform the any of the split functions.

Although you have said that you know it is a bad design, I can't in good conscience not mention it in my answer, so whichever method you choose is not a substitute for fixing how this is stored. If not by you, by whoever designed it.

The correct method would be a junction table, tblLoginArea:

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

Then if the developers still need the csv format, then they can create a view, and update their references to that:

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);

And your query can be done using equality predicates that can be optimised with indexed:

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;

Example on DB Fiddle

Answer 3

Consider this split function:

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
    );

Then you can do a query like this:

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