sql中某一个字段内容为用逗号分割的字符串转换成多条数据

效果：

SQL语句

 1 DECLARE @table1 TABLE (
 2     Code NVARCHAR(50), --班别
 3     NAME NVARCHAR(50)  --名字
 4 )
 5 INSERT INTO @table1 VALUES ('大班', '张三,李四，王五')
 6 INSERT INTO @table1 VALUES ('中班', '小美,小可')
 7 INSERT INTO @table1 VALUES ('小班', '小甜')
 8 SELECT * FROM @table1;
 9 
10 WITH Temp AS (
11         SELECT A.Code, A.NAME
12             , CHARINDEX(',', A.NAME + ',') AS STA
13             , CHARINDEX(',', A.NAME + ',') - 1 AS LENS
14         FROM @table1 A
15         UNION ALL
16         SELECT Code, NAME
17             , CHARINDEX(',', NAME + ',', STA + 1) AS STA
18             , CHARINDEX(',', NAME + ',', STA + 1) - STA - 1 AS LENS
19         FROM Temp
20         WHERE STA <> 0
21     )
22 SELECT Code, SUBSTRING(NAME, STA - LENS, LENS) AS Name
23 FROM Temp
24 WHERE STA <> 0
25 ORDER BY Code