create table account(sname varchar(20),sshy varchar(100),khgzr varchar(20)) --主要表

insert into account values("客戶1","化工;石油","員工1") --主要插入測試資料

insert into account values("客戶2","化工;石油","員工2")

insert into account values("客戶3","化工;石油","員工3")

insert into account values("客戶4","化工;石油;機械","員工4")

insert into account values("客戶5","化工;石油;機械","員工5")

create table account5(sname varchar(20),sshy varchar(100),khgzr varchar(20)) --需求表

create table tmp(sname varchar(20),sshy varchar(100),khgzr varchar(20)) --臨時表,因為可能需求表有主鍵 不讓插入空列

declare cursor1 cursor for --使用遊標遍歷所有內容，對欄位sshy進行拆分

select sname,sshy,khgzr from account

open cursor1

declare @sname varchar(20)

declare @sshy varchar(100)

declare @khgzr varchar(20)

DECLARE @str varchar(4000)

fetch next from cursor1 into @sname,@sshy,@khgzr

while @@FETCH_STATUS=0

begin

SET @str="insert into tmp (sshy) SELECT "+""""+ REPLACE(@sshy,";",""" union all select """)+"""" --拼接語句

exec (@str) --執行拼接的語句

update tmp set sname=@sname,khgzr=@khgzr --更新欄位

insert into account5 select sname,sshy,khgzr from tmp --把更新完的寫入需求表

fetch next from cursor1 into @sname,@sshy,@khgzr

end

close cursor1

deallocate cursor1

select * from account5