SELECT y.Name, count(*) Count
FROM
(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)
) y
GROUP BY y.Name
结果:
Name Count
alax 2
john 2
rock 3
sira 2
smith 2