Single value from a list of comma separated values when used in inner join in SQL Server

Question

Can anyone please help me with a query to return a single value from a list of comma separated values when used in inner join. I can paste the example query here.

select * 
from Test.dbo.Test1 
   INNER JOIN  @TestTable T ON T.Name = ???? 

The table given is Test.dbo.Test1 .I had inserted the values to a table variable @TestTable and doing an inner join. The table is:-

Id  Name
1   John,Mili,Anil,Abhi
2   John,Abhi
3   John
4   Mili,Anil,Abhi
5   Anil
6   John,Mili,Anil,Abhi

I need a query in '???' space where I can select a single value from comma separated values in 'name' field and compare with the other.

Answer 1

mdbo and @ are hints it's MS Sql Server. Use a splitter table valued function of choice, Jeff Moden's one http://www.sqlservercentral.com/articles/Tally+Table/72993/ performs very good. So

select * 
from Test.dbo.Test1 a
   INNER JOIN  @TestTable T on T.Name in (select item from [DelimitedSplit8K](a.Name, ','))

But may I say the best way to solve the problem is to have no such a problem. Any chance Test.dbo.Test1 could be refactored to have a separate row for every item from Name column? Then take this opportunity.

it's possible to use plain like, but note it prevents optimizer from using any index. In the above version at least index on T.Name can be used.

select * 
    from Test.dbo.Test1 a
       INNER JOIN  @TestTable T on ','+a.Name+',' like '%,'+T.name+',%'