格式化拆分栏位

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Sheet1') is null
	drop table Sheet1
Go
Create table Sheet1([授课教师] nvarchar(179))
Insert Sheet1
select N'叶宏[112017],李广军[112123]' union all
select N'韩翔2[004041]' union all
select N'张文[334104],丁丁[334137],郭子健[334125],赵红[334110],杨数清[334101],王敏敏[334112],丹丹[334138]' union all
select N'张文[334104],丁丁[334137],郭子健[334125],郑娟娟[334108]'

GO
SELECT a.授课教师
	  ,STUFF(a.Cols.query('/R/Col[position() mod 2=1]/text()').value('.','nvarchar(1000)'),1,1,'') AS 姓名
	  ,STUFF(a.Cols.query('/R/Col[position() mod 2=0]/text()').value('.','nvarchar(1000)'),1,1,'') AS 编号
FROM (SELECT *
			,CONVERT(
				 XML
				,'<R><Col>;'+REPLACE(REPLACE(REPLACE([授课教师], '[', '</Col><Col>;'), '],', '</Col><Col>;'), ']', '</Col></R>')) AS Cols
	  FROM [dbo].[Sheet1]) AS a 
GO

 

显示效果：
 

来自网友问题：
网友提问