sql 把一列的数据按逗号分隔转换成多行

时间:2022-05-05 10:35:24

ALTER proc [dbo].[ModifyWkCashAccountNo]
as
begin
set xact_abort on
begin transaction
declare @errors int --错误数
--分列

WHILE (select COUNT(*) from ModifyWkContent where state=0 and results1='0000' and results2='0000' and results3='0000' and results4='0000' and results5='0000'
and results6='0000' and results7='0000') >0
BEGIN


IF Object_id('Tempdb..#t') IS NOT NULL
DROP TABLE #t --如果有存在就删除临时表
set @errors=@errors+@@error

IF Object_id('Tempdb..#t2') IS NOT NULL
DROP TABLE #t2 --如果有存在就删除临时表
set @errors=@errors+@@error

select top 1 * into #t from ModifyWkContent where state=0 and results1='0000' and results2='0000' and results3='0000' and results4='0000' and results5='0000'
and results6='0000' and results7='0000'
set @errors=@errors+@@error

declare @WLCInvestorApplyID_Messge nvarchar(max);
set @WLCInvestorApplyID_Messge =(select WLCInvestorApplyID_Messge from #t)
set @errors=@errors+@@error

select * into #t2 from dbo.Split(@WLCInvestorApplyID_Messge,',')
set @errors=@errors+@@error

insert into ModifyWkContent2 select OldCashAccountNo,NewCashAccountNo,t2.name,results1,results2,results3,results4,results5,results6,results7,state
from #t2 t2 , #t t1
set @errors=@errors+@@error

update ModifyWkContent set state=1 where id in(select id from #t)
set @errors=@errors+@@error

IF (select COUNT(*) from ModifyWkContent where state=0 and results1='0000' and results2='0000' and results3='0000' and results4='0000' and results5='0000'
and results6='0000' and results7='0000') <=0
BREAK
ELSE
CONTINUE
END

if(@errors<>0)
begin
select 'Error'
rollback tran
--return;
end
else
begin
commit;
select 'OK'
end

end