select 'T1234, C1234, D1234, C1234' as col1 union all

select 'E1234, C1234'

),

cte as (

select col1,

convert(varchar(max), (case when col1 like 'C%' then ', ' + left(col1, charindex(',', col1 + ',') - 1 ) else '' end)) as c_list,

convert(varchar(max), stuff(col1, 1, charindex(',', col1 + ',') + 1, '')) as rest,

1 as lev

from t

union all

select col1,

c_list + (case when rest like 'C%' then ', ' + left(rest, charindex(',', rest + ',') - 1 ) else '' end) ,

convert(varchar(max), stuff(rest, 1, charindex(',', rest + ',') + 1, '')) as rest,

lev + 1

from cte

where rest > '' and lev < 10

)

select stuff(c_list, 1, 2, '') as c_list

from (select cte.*, row_number() over (partition by col1 order by lev desc) as seqnum

from cte

) cte

where seqnum = 1