SQL 拆分字符串,该如何处理

SQL 拆分字符串

CREATE TABLE dbo.Employees
(
  Class    VarChar(40)   NOT NULL,
  Teacher  VarChar(200)  NOT NULL,
);
Insert Into dbo.Employees(Class, Teacher)  Values('001','张三,李四,王二');
Insert Into dbo.Employees(Class, Teacher)  Values('002','李四');
Insert Into dbo.Employees(Class, Teacher)  Values('003','李四,王二');
Insert Into dbo.Employees(Class, Teacher)  Values('004','张三,王二');
Insert Into dbo.Employees(Class, Teacher)  Values('005','张三');
Insert Into dbo.Employees(Class, Teacher)  Values('006','张三,李四,王二');
Insert Into dbo.Employees(Class, Teacher)  Values('007','李四,王二');
Insert Into dbo.Employees(Class, Teacher)  Values('008','张三,李四,王二');
Insert Into dbo.Employees(Class, Teacher)  Values('009','张三,王二');

期望获得结果：
Teacher  Count  
 张三    6
 李四    6
 王二    7
------解决思路----------------------

SELECT SUBSTRING(T1.Teacher,T2.number,CHARINDEX(',',T1.Teacher+',',T2.number)-T2.number)Teacher
	,COUNT(1)[Count]
FROM Employees T1
	JOIN master..spt_values T2 ON T2.type='P'
WHERE CHARINDEX(',',','+T1.Teacher,T2.number)=T2.number
GROUP BY SUBSTRING(T1.Teacher,T2.number,CHARINDEX(',',T1.Teacher+',',T2.number)-T2.number)

------解决思路----------------------
CREATE TABLE #Employees

(

  Class    VarChar(40)   NOT NULL,

  Teacher  NVarChar(200)  NOT NULL,

);

Insert Into #Employees(Class, Teacher)  Values('001',N'张三,李四,王二');

Insert Into #Employees(Class, Teacher)  Values('002',N'李四');

Insert Into #Employees(Class, Teacher)  Values('003',N'李四,王二');

Insert Into #Employees(Class, Teacher)  Values('004',N'张三,王二');

Insert Into #Employees(Class, Teacher)  Values('005',N'张三');

Insert Into #Employees(Class, Teacher)  Values('006',N'张三,李四,王二');

Insert Into #Employees(Class, Teacher)  Values('007',N'李四,王二');

Insert Into #Employees(Class, Teacher)  Values('008',N'张三,李四,王二');

Insert Into #Employees(Class, Teacher)  Values('009',N'张三,王二');

 
with cte as
(
select *,
COL=convert(xml,'<v><v>'+replace(replace(rtrim(ltrim(Teacher)),',','</v><v>'),'<v></v>','')+'</v></v>')   from #Employees a
)
,cte1 as
(
select * from cte A
outer apply (select col1=T.C.value('.','nvarchar(100)') from A.COL.nodes('/v/v')T(C)) B
)
select col1,COUNT(*) 
from cte1 group by col1