一个查询的有关问题，

一个查询的问题，急！！！

C# code

CREATE TABLE #t1(AFromID INT,AToUserId INT,AUserName VARCHAR(20),BFromID INT,BToUserId INT, BUserName VARCHAR(20),RelationID VARCHAR(1000),RelationName VARCHAR(1000))INSERT INTO #t1(AFromID,AToUserId,AUserName,BFromID,BToUserId, BUserName)SELECT 16,6,'黄永友',6,207,'华附物理深化' UNIONSELECT 16,6,'黄永友',6,7,'卢福东' UNIONSELECT 16,5,'叶正波',5,8,'赵建辉' UNIONSELECT 16,6,'黄永友',6,8,'赵建辉'select * FROM #t1drop table #t1/*现在的结果16    5    叶正波    5    8    赵建辉    NULL    NULL16    6    黄永友    6    7    卢福东    NULL    NULL16    6    黄永友    6    8    赵建辉    NULL    NULL16    6    黄永友    6    207    华附    NULL    NULL想要的结果16    5    叶正波    5    8    赵建辉    5,6    叶正波,黄永友16    6    黄永友    6    7    卢福东    6    黄永友16    6    黄永友    6    207    华附    6    黄永友*/

SQL code

CREATE TABLE #t1(AFromID INT,AToUserId INT,AUserName VARCHAR(20),BFromID INT,BToUserId INT, BUserName VARCHAR(20),RelationID VARCHAR(1000),RelationName VARCHAR(1000))INSERT INTO #t1(AFromID,AToUserId,AUserName,BFromID,BToUserId, BUserName)SELECT 16,6,'黄永友',6,207,'华附物理深化' UNIONSELECT 16,6,'黄永友',6,7,'卢福东' UNIONSELECT 16,5,'叶正波',5,8,'赵建辉' UNIONSELECT 16,6,'黄永友',6,8,'赵建辉'select * FROM #t1;--drop table #t1with TT as(select *,ROW_NUMBER() over(partition by BToUserId order by atouserid) as rowidfrom #t1)select x.*,y.RelationID,y.RelationNamefrom (select AFromID,AToUserId,AUserName,BFromID,BToUserId, BUserNamefrom TT where rowid in (select min(rowid) from TT group by BToUserId)) as xinner join (select BToUserId,BUserName,stuff((select ','+CAST(atouserid as varchar(20)) from #t1  as b where b.BToUserId=a.BToUserId for xml path('')),1,1,'') as RelationID,stuff((select ','+CAST(AUserName as varchar(20)) from #t1  as c where c.BToUserId=a.BToUserId for xml path('')),1,1,'') as RelationNamefrom #t1 as agroup by BToUserId,BUserName) as y on x.BToUserId=y.BToUserId