求大神解答
------解决方案--------------------
;with f as
(
select a.id,a.name+':'+b.name as name from a inner join b on a.name=b.id
)
select
id,stuff((select ','+[name] from f where id=t.id for xml path('')), 1, 1, '')
from
f as t
group by
id
------解决方案--------------------
create table tableA(ID int,NAME char)
insert tableA
select 1,'a' union all
select 2,'b' union all
select 3,'c'
create table tableB(ID char,NAME char)
insert tableB
select 'a','q' union all
select 'a','w' union all
select 'a','e' union all
select 'B','z' union all
select 'B','x' union all
select 'B','c' union all
select 'c','b' union all
select 'c','n' union all
select 'c','m'
SELECT ID,
NAME=STUFF((
SELECT ','+NAME
FROM (select ID=a.ID,NAME=a.NAME+':'+b.NAME
from tableA a
inner join tableB b
on a.NAME=lower(b.ID)) t
WHERE t.ID=t1.ID FOR XML PATH('')), 1, 1, '')
FROM (
select ID=a.ID,NAME=a.NAME+':'+b.NAME
from tableA a
inner join tableB b
on a.NAME=lower(b.ID)
) t1
GROUP BY ID
/*
1 a:q,a:w,a:e
2 b:z,b:x,b:c
3 c:b,c:n,c:m
*/
------解决方案--------------------
你的for xml path呢?