name| No| Desc| classs| stu 某某|1| 123,456,1254,123456| 102 小明|2| 5321,123
create table tableEnd (name varchar(6), Nos int, Descs varchar(50))insert into tableEnd select '某某', 1, '123,456,1254,123456' union all select '小明', 2, '5321,123' create table tableRaw (name varchar(6), Nos int, Descs varchar(50))
insert into tableRaw select '某某', 1, '123' union all select '小明', 2, '5321' union all select '某某', 1, '555' union all select '某某', 1, '666' union all select '小黑', 3, '456'select * from tableEndname Nos Descs ------ ----------- -------------------------------------------------- 某某 1 123,456,1254,123456 小明 2 5321,123select * from tableRawname Nos Descs ------ ----------- -------------------------------------------------- 某某 1 123 小明 2 5321 某某 1 555 某某 1 666 小黑 3 456 ;with t as (select a.name,a.Nos,substring(a.Descs,b.number,charindex(',',a.Descs+',',b.number)-b.number) Descs from tableEnd a inner join master.dbo.spt_values b on b.[type]='P' and substring(','+a.Descs,b.number,1)=',' union select name,Nos,Descs from tableRaw ) select name,Nos, stuff( (select ','+Descs from t t2 where t2.name=t.name and t2.Nos=t.Nos for xml path('')), 1,1,'') Descs from t group by name,Nosname Nos Descs ------ ----------- ------------------------------- 某某 1 123,123456,1254,456,555,666 小明 2 123,5321 小黑 3 456(3 row(s) affected)
create table tableEnd (name varchar(6), Nos int, Descs varchar(50))insert into tableEnd select '某某', 1, '123,456,1254,123456' union all select '小明', 2, '5321,123' create table tableRaw (name varchar(6), Nos int, Descs varchar(50))
insert into tableRaw select '某某', 1, '123' union all select '小明', 2, '5321' union all select '某某', 1, '555' union all select '某某', 1, '666' union all select '小黑', 3, '456'select * from tableEndname Nos Descs ------ ----------- -------------------------------------------------- 某某 1 123,456,1254,123456 小明 2 5321,123select * from tableRawname Nos Descs ------ ----------- -------------------------------------------------- 某某 1 123 小明 2 5321 某某 1 555 某某 1 666 小黑 3 456 ;with t as (select a.name,a.Nos,substring(a.Descs,b.number,charindex(',',a.Descs+',',b.number)-b.number) Descs from tableEnd a inner join master.dbo.spt_values b on b.[type]='P' and substring(','+a.Descs,b.number,1)=',' union select name,Nos,Descs from tableRaw ) merge tableEnd as o using (select name,Nos, stuff( (select ','+Descs from t t2 where t2.name=t.name and t2.Nos=t.Nos for xml path('')), 1,1,'') Descs from t group by name,Nos) as s on o.Nos=s.Nos and o.name=s.name when matched then update set o.Descs=s.Descs when not matched then insert(name,Nos,Descs) values(name,Nos,Descs);-- 结果 select * from tableEndname Nos Descs ------ ----------- -------------------------------------------------- 某某 1 123,123456,1254,456,555,666 小明 2 123,5321 小黑 3 456(3 row(s) affected)
name| No| Desc| classs| stu
某某|1| 123,456,1254,123456| 102
小明|2| 5321,123
create table tableEnd
(name varchar(6), Nos int, Descs varchar(50))insert into tableEnd
select '某某', 1, '123,456,1254,123456' union all
select '小明', 2, '5321,123' create table tableRaw
(name varchar(6), Nos int, Descs varchar(50))
insert into tableRaw
select '某某', 1, '123' union all
select '小明', 2, '5321' union all
select '某某', 1, '555' union all
select '某某', 1, '666' union all
select '小黑', 3, '456'select * from tableEndname Nos Descs
------ ----------- --------------------------------------------------
某某 1 123,456,1254,123456
小明 2 5321,123select * from tableRawname Nos Descs
------ ----------- --------------------------------------------------
某某 1 123
小明 2 5321
某某 1 555
某某 1 666
小黑 3 456
;with t as
(select a.name,a.Nos,substring(a.Descs,b.number,charindex(',',a.Descs+',',b.number)-b.number) Descs
from tableEnd a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.Descs,b.number,1)=','
union
select name,Nos,Descs from tableRaw
)
select name,Nos,
stuff(
(select ','+Descs from t t2
where t2.name=t.name and t2.Nos=t.Nos for xml path('')),
1,1,'') Descs
from t
group by name,Nosname Nos Descs
------ ----------- -------------------------------
某某 1 123,123456,1254,456,555,666
小明 2 123,5321
小黑 3 456(3 row(s) affected)
create table tableEnd
(name varchar(6), Nos int, Descs varchar(50))insert into tableEnd
select '某某', 1, '123,456,1254,123456' union all
select '小明', 2, '5321,123' create table tableRaw
(name varchar(6), Nos int, Descs varchar(50))
insert into tableRaw
select '某某', 1, '123' union all
select '小明', 2, '5321' union all
select '某某', 1, '555' union all
select '某某', 1, '666' union all
select '小黑', 3, '456'select * from tableEndname Nos Descs
------ ----------- --------------------------------------------------
某某 1 123,456,1254,123456
小明 2 5321,123select * from tableRawname Nos Descs
------ ----------- --------------------------------------------------
某某 1 123
小明 2 5321
某某 1 555
某某 1 666
小黑 3 456
;with t as
(select a.name,a.Nos,substring(a.Descs,b.number,charindex(',',a.Descs+',',b.number)-b.number) Descs
from tableEnd a
inner join master.dbo.spt_values b
on b.[type]='P' and substring(','+a.Descs,b.number,1)=','
union
select name,Nos,Descs from tableRaw
)
merge tableEnd as o
using
(select name,Nos,
stuff(
(select ','+Descs from t t2
where t2.name=t.name and t2.Nos=t.Nos for xml path('')),
1,1,'') Descs
from t group by name,Nos) as s
on o.Nos=s.Nos and o.name=s.name
when matched then
update set o.Descs=s.Descs
when not matched then
insert(name,Nos,Descs)
values(name,Nos,Descs);-- 结果
select * from tableEndname Nos Descs
------ ----------- --------------------------------------------------
某某 1 123,123456,1254,456,555,666
小明 2 123,5321
小黑 3 456(3 row(s) affected)