sql 求助，一定要弄清楚这个问题。。每次这里卡死。。
里面有测试表和数据，我不得不提问，大神们知道下，怎么解决这个老要我聚合rn的方法，不是一，二次碰到了。。
CREATE TABLE #a(ID INT,name VARCHAR(200),pass VARCHAR(200),remark VARCHAR(200))
INSERT INTO #a
SELECT 1,'1','1','AB'
UNION ALL
SELECT 2,'1','3','BCD'
CREATE TABLE #b(ID INT,fieldvalue VARCHAR(200),fieldremark VARCHAR(200))
INSERT INTO #b
SELECT 1,'A','备注1'
UNION ALL
SELECT 2,'B','备注2'
UNION ALL
SELECT 3,'C','备注3'
UNION ALL
SELECT 4,'D','备注4'
select * from #a a
;with ceb as
(
select a.ID,a.name,a.pass,substring(a.remark,number+1,1) as fieldvalue from #a a
left join
(
select number from master..spt_values t where t.type='p' ) b
on (number+1)<=len(a.remark)
)
,ceb2 as
(
select ROW_NUMBER() over(order by getdate()) rn,a.ID,a.name,a.pass,b.fieldremark from ceb a,#b b where a.fieldvalue=b.fieldvalue
)
select ID, name, pass,
stuff((select ','+b.fieldremark from ceb2 b where a.rn=b.rn for xml path('')),1,1,'') as fieldremark
from ceb2 a group by ID,name,pass
;with ceb as
(
select a.ID,a.name,a.pass,substring(a.remark,number+1,1) as fieldvalue from #a a
left join
(
select number from master..spt_values t where t.type='p' ) b
on (number+1)<=len(a.remark)
),ceb2 as
(
select a.ID,a.name,a.pass,b.fieldremark from ceb a,#b b where a.fieldvalue=b.fieldvalue
)
select a.id,a.NAME,a.pass,
stuff((select ','+fieldremark from ceb2 b
where b.id=a.id and b.name=a.NAME AND a.pass=b.pass
for xml path('')),1,1,'') 'fieldremark'
from ceb2 a
group by a.id,a.NAME,a.pass
/*
id NAME pass fieldremark
----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 1 备注1,备注2
2 1 3 备注2,备注3,备注4
*/
