join comma delimited data column

Ideally, your best solution would be to normalize Table2 so you are not storing a comma separated list.

Once you have this data normalized then you can easily query the data. The new table structure could be similar to this:

CREATE TABLE T1
(
  [col1] varchar(2), 
  [col2] varchar(5),
  constraint pk1_t1 primary key (col1)
);

INSERT INTO T1
    ([col1], [col2])
VALUES
    ('C1', 'john'),
    ('C2', 'alex'),
    ('C3', 'piers'),
    ('C4', 'sara')
;

CREATE TABLE T2
(
  [col1] varchar(2), 
  [col2] varchar(2),
  constraint pk1_t2 primary key (col1, col2),
  constraint fk1_col2 foreign key (col2) references t1 (col1)
);

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

Normalizing the tables would make it much easier for you to query the data by joining the tables:

select t2.col1, t1.col2
from t2
inner join t1
  on t2.col2 = t1.col1

See Demo

Then if you wanted to display the data as a comma-separated list, you could use FOR XML PATH and STUFF:

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, '') col2
from t2;

See Demo.

If you are not able to normalize the data, then there are several things that you can do.

First, you could create a split function that will convert the data stored in the list into rows that can be joined on. The split function would be similar to this:

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;

When you use the split, function you can either leave the data in the multiple rows or you can concatenate the values back into a comma separated list:

;with cte as
(
  select c.col1, t1.col2
  from t1
  inner join 
  (
    select t2.col1, i.items 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, '') col2
from cte c

See Demo.

A final way that you could get the result is by applying FOR XML PATH directly.

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;

See SQL Fiddle with Demo

Here's a way of splitting the data without a function, then using the standard XML PATH method for getting the CSV list:

with CTE as
(
  select T2.col1
    , T1.col2
  from T2
    inner join T1 on charindex(',' + T1.col1 + ',', ',' + T2.col2 + ',') > 0
)
select T2.col1
  , col2 = stuff(
      (
        select ',' + CTE.col2
        from CTE
        where T2.col1 = CTE.col1
        for xml path('')
      )
      , 1
      , 1
      , ''
    )
from T2

SQL Fiddle with demo.

As has been mentioned elsewhere in this question it is hard to query this sort of denormalised data in any sort of efficient manner, so your first priority should be to investigate updating the table structure, but this will at least allow to get the results you require.

If you wanted to do this task in oracle we can use listagg and can accomplish this easily.

A possible equivalent available in SQL Server for listagg is Stuff

So using stuff you can try with following query:

SELECT T2.Col1,
       Stuff((SELECT ',' + CAST(T1.Col2 AS VARCHAR(100))
               FROM T1
              WHERE T2.Col2 LIKE T1.Col1
                FOR Xml Path('')),
             1,
             1,
             '')
  FROM T2