从SQL Server中的表中拆分逗号分隔值

SELECt y.Name, count(*) CountFROM(VALUES('john,smith,alax,rock'),('smith,alax,sira'),('john,rock'),('rock,sira')) x(names)CROSS APPLY(SELECt t.c.value('.', 'VARCHAr(2000)') Name     FROM (         SELECT x = CAST('<t>' +     REPLACE(x.names, ',', '</t><t>') + '</t>' AS XML)     ) a     CROSS APPLY x.nodes('/t') t(c)     ) yGROUP BY y.Name

结果：

Name   Countalax   2john   2rock   3sira   2smith  2