表A
ID VALUES
1 a,b,c
2 c,d
3 null
4 e表B
ID text value
1 一 a
2 二 b
3 五 c
4 六 d想写SQL 得
ID 字段A 字段B
1 a,b,c 一,二,五
2 c,d 五,六
3 null null
4 e null请问怎么写
ID VALUES
1 a,b,c
2 c,d
3 null
4 e表B
ID text value
1 一 a
2 二 b
3 五 c
4 六 d想写SQL 得
ID 字段A 字段B
1 a,b,c 一,二,五
2 c,d 五,六
3 null null
4 e null请问怎么写
create table ta(id int ,val varchar(100))
insert into ta(id,val)
select 1,'a,b,c'
union all select 2 ,'c,d'
union all select 3,null
union all select 4,'e'create table tb(id int ,[text] varchar(100),val varchar(100))
insert into tb(id,[text],val)
select 1,'一','a'
union all select 2 ,'二','b'
union all select 3,'五','c'
union all select 4,'六','d'select * from ta
select * from tb
create function dbo.fn_name
(@v varchar(100))
returns varchar(100)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[text]+','
from tb
where charindex(val,@v)>0
if (len(@s)>0)
set @s= left(@s,len(@s)-1)
return @s
end/*
drop function dbo.fn_name
drop table ta,tb
*/select *,dbo.fn_name(isnull(val,'')) as val2
from ta======================
1 a,b,c 一,二,五
2 c,d 五,六
3 NULL
4 e
(
select 1,'a,b,c'
union all select 2 ,'c,d'
union all select 3,null
union all select 4,'e'
)
,b (ID,[text],[value]) as
(
select 1,'一','a'
union all select 2 ,'二','b'
union all select 3,'五','c'
union all select 4,'六','d'
)
,c1 as
(
select b.[text],a.id,a.[values]
from b cross join a
where charindex(b.[value],a.[values])>0 or a.[values] is null
)
,c2 as
(
select null as [text],a.id,a.[values]
from b cross join a
where a.[values] is not null and not exists(select * from c1 where a.[values]=c1.[values])
)
,c as
(
select * from c1
union all
select * from c2
)
SELECT d.id,d.[values],[text]=stuff
(
(select ','+RTRIM([text]) as [text()]
from c e where d.id=e.id and d.[values]=e.[values]
for xml path('')
),1,1,'') FROM c d group by d.id,d.[values]
(
(select ','+RTRIM([text])
from c e where d.id=e.id and d.[values]=e.[values]
for xml path('')
),1,1,'') FROM c d group by d.id,d.[values]