create table a表(字段A varchar(10))insert into a表(字段A) select 'a,b'create table b表(字段A varchar(10))insert into b表(字段A) select 'b' union all select 'c' select a.字段A+cast((select ','+b.字段A from b表 b where charindex(b.字段A,a.字段A,1)=0 for xml path('')) as varchar) '字段A' from a表 a/* 字段A ---------------------------------------- a,b,c(1 row(s) affected) */
if OBJECT_ID('test') is not null drop table test go create table test (
[key] varchar(20) ) go insert test select 'a,b' union all select 'c' select * from test/* id key ----------- -------------------- 1 a,b 2 c(2 行受影响) */ select distinct SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] from test a,master..spt_values where number >=1 and number<=len([key]) and [type]='p' and substring(','+[key],number,1)=',' /* key -------------------- a b c(3 行受影响) */ --按单据编号进行分组合并 SELECT distinct stuff((SELECT ','+[key] FROM test FOR XML PATH('') ),1,1,'') AS [key] FROM test a GROUP BY [key]/* key --------------------- a,b,c(1 行受影响) */
insert into a表(字段A) select a.字段A+cast((select ','+b.字段A from b表 b where charindex(b.字段A,a.字段A,1)=0 for xml path('')) as varchar) '字段A' from a表 a
insert into a表(字段A) select a.字段A+cast((select ','+b.字段A from b表 b where charindex(b.字段A,a.字段A,1)=0 for xml path('')) as varchar) '字段A' from a表 a
刚刚表达错了,存在就不处理,不存在的话,就更新
update a set a.字段A=a.字段A+cast((select ','+b.字段A from b表 b where charindex(b.字段A,a.字段A,1)=0 for xml path('')) as varchar) from a表 a
update a set a.字段A=a.字段A+cast((select ','+b.字段A from b表 b where charindex(b.字段A,a.字段A,1)=0 for xml path('')) as varchar) from a表 a
create table a表(字段A varchar(10))insert into a表(字段A)
select 'a,b'create table b表(字段A varchar(10))insert into b表(字段A)
select 'b' union all
select 'c'
select a.字段A+cast((select ','+b.字段A
from b表 b
where charindex(b.字段A,a.字段A,1)=0
for xml path('')) as varchar) '字段A'
from a表 a/*
字段A
----------------------------------------
a,b,c(1 row(s) affected)
*/
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
[key] varchar(20)
)
go
insert test
select 'a,b' union all
select 'c'
select * from test/*
id key
----------- --------------------
1 a,b
2 c(2 行受影响)
*/
select
distinct
SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
from
test a,master..spt_values
where
number >=1 and number<=len([key])
and [type]='p'
and substring(','+[key],number,1)=','
/*
key
--------------------
a
b
c(3 行受影响)
*/
--按单据编号进行分组合并
SELECT distinct
stuff((SELECT ','+[key] FROM test
FOR XML PATH('')
),1,1,'') AS [key]
FROM test a
GROUP BY [key]/*
key
---------------------
a,b,c(1 行受影响)
*/
insert into a表(字段A)
select a.字段A+cast((select ','+b.字段A
from b表 b
where charindex(b.字段A,a.字段A,1)=0
for xml path('')) as varchar) '字段A'
from a表 a
insert into a表(字段A)
select a.字段A+cast((select ','+b.字段A
from b表 b
where charindex(b.字段A,a.字段A,1)=0
for xml path('')) as varchar) '字段A'
from a表 a
刚刚表达错了,存在就不处理,不存在的话,就更新
update a
set a.字段A=a.字段A+cast((select ','+b.字段A
from b表 b
where charindex(b.字段A,a.字段A,1)=0
for xml path('')) as varchar)
from a表 a
update a
set a.字段A=a.字段A+cast((select ','+b.字段A
from b表 b
where charindex(b.字段A,a.字段A,1)=0
for xml path('')) as varchar)
from a表 a
最后请教一个问题:有多个这样字段,例如还有一个字段B 表达方式也是跟字段A一样的