declare @t table (s varchar(100));
insert into @t values ('Привет, 1, привет,1');

select
 b.x.value('item[1]', 'varchar(100)'),
 b.x.value('item[2]', 'varchar(100)'),
 b.x.value('item[3]', 'varchar(100)'),
 b.x.value('item[4]', 'varchar(100)')
from
 @t t cross apply
 (select cast(cast('' as xml).query('sql:column("t.s")') as varchar(max))) a(s) cross apply
 (select cast('<item>' + replace(a.s, ',', '</item><item>') + '</item>' as xml)) b(x);