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

[qiudong_5210]

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,'华附物理深化' UNION
SELECT 16,6,'黄永友',6,7,'卢福东' UNION
SELECT 16,5,'叶正波',5,8,'赵建辉' UNION
SELECT 16,6,'黄永友',6,8,'赵建辉'
select * FROM #t1
drop table #t1

/*
现在的结果
16	5	叶正波	5	8	赵建辉	NULL	NULL
16	6	黄永友	6	7	卢福东	NULL	NULL
16	6	黄永友	6	8	赵建辉	NULL	NULL
16	6	黄永友	6	207	华附	NULL	NULL

想要的结果
16	5	叶正波	5	8	赵建辉	5,6	叶正波,黄永友
16	6	黄永友	6	7	卢福东	6	黄永友
16	6	黄永友	6	207	华附	6	黄永友

*/

[kensouterry1]

合并行集？

[qiudong_5210]

[Quote=引用 1 楼 的回复:]
合并行集？
[/Quote]

如果BToUserId数值相同，那么就合并AToUserId和AUserName中的数据

[开启时代]

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,'华附物理深化' UNION
SELECT 16,6,'黄永友',6,7,'卢福东' UNION
SELECT 16,5,'叶正波',5,8,'赵建辉' UNION
SELECT 16,6,'黄永友',6,8,'赵建辉'
select * FROM #t1;
--drop table #t1
with TT as(
select *,ROW_NUMBER() over(partition by BToUserId order by atouserid) as rowid
from #t1)

select x.*,y.RelationID,y.RelationName
from 
(
select AFromID,
AToUserId,
AUserName,
BFromID,
BToUserId, 
BUserName
from TT 
where rowid in (select min(rowid) from TT group by BToUserId)) as x
inner 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 RelationName
from #t1 as a
group by BToUserId,BUserName) as y on x.BToUserId=y.BToUserId