sql中如何利用分隔符将一条数据生成多条数据

select InvolvedDept+'、' from tb_TXLC where  FlowType='EQYS'  and OtherNum2=@EquipAID

--设备来自这张表tb_EquipTempSB
select @InvolvedDept=InvolvedDept+'、' from tb_TXLC where OtherNum2=@EquipAID and FlowType='EQYS'

select @SBFlow2Person=Flow2Person from tb_TXLC where OtherNum2=@EquipAID and FlowType='EQYS'
select @SBCreateDocPath=CreateDocPath from tb_TXLC where OtherNum2=@EquipAID and FlowType='EQYS'
select @SBText101=Text101 from tb_TXLC where OtherNum2=@EquipAID and FlowType='EQYS'
select @SBContactTel=ContactTel from tb_TXLC where OtherNum2=@EquipAID and FlowType='EQYS'

SET @InvolvedDept=REPLACE(@InvolvedDept,'、 ', '、' )

此句的作用是防止用,来替换@InvolvedDept中出现的逗号空格，防止用户输入逗号的时候，一不小心输入了一个空格

set @split='、'

只要字符串中有、
WHILE (CHARINDEX(@split,@InvolvedDept)<>0)
BEGIN

insert into tb_Equip
(EquipTreeID,FlowNode,TotalStatus,EquipStatus,EquipType,TestScope,AssetNumber,EquipName,Manufacturer,
CaliPerson,TestItem,Creator,CreateTime)
select
73139,'待复核','审核中','完好闲置','SBTZ',@SBContactTel,@SBText101,@SBFlow2Person,@SBCreateDocPath,
'金试',SUBSTRING(@InvolvedDept,1,CHARINDEX(@split,@InvolvedDept)-1),@UserName,getdate()

CHARINDEX(@split,@InvolvedDept)-1 第一个、出现的位置是9,所以从字符串中截取8位就可以得到第一条数据的编号

sql stuff函数用于删除指定长度的字符，下面的语句代表从第一个位置开始删除长度为9的字符

SET @InvolvedDept=STUFF(@InvolvedDept,1,CHARINDEX(@split,@InvolvedDept),'')

END