22,300
社区成员




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 黄永友
*/
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