Hi,
Please refer below Code.
SQL
DECLARE @User AS TABLE(Id INT,UserName VARCHAR(50),idCV INT,[Status] BIT)
INSERT INTO @User VALUES(1,'user1',1,0)
INSERT INTO @User VALUES(2,'user2',1,0)
INSERT INTO @User VALUES(3,'user3',1,0)
INSERT INTO @User VALUES(4,'user4',1,0)
INSERT INTO @User VALUES(5,'user5',1,0)
INSERT INTO @User VALUES(6,'user1',2,0)
INSERT INTO @User VALUES(7,'user2',2,0)
INSERT INTO @User VALUES(8,'user1',3,0)
INSERT INTO @User VALUES(9,'user2',3,0)
INSERT INTO @User VALUES(10,'user3',3,0)
INSERT INTO @User VALUES(11,'user1',4,0)
INSERT INTO @User VALUES(12,'user2',4,0)
INSERT INTO @User VALUES(13,'user3',4,0)
INSERT INTO @User VALUES(14,'user5',4,0)
INSERT INTO @User VALUES(15,'user6',4,0)
INSERT INTO @User VALUES(16,'user8',4,0)
INSERT INTO @User VALUES(17,'user9',4,0)
DECLARE @CongVan AS TABLE(idCV INT,SoCV VARCHAR(50))
INSERT INTO @CongVan VALUES(1,'922/QD-HDQT-TDHO')
INSERT INTO @CongVan VALUES(2,'85-TB/BCSD-NHNN')
INSERT INTO @CongVan VALUES(3,'286/CD-NHNO')
INSERT INTO @CongVan VALUES(4,'388/QD/NHNO-TP-HCNS')
INSERT INTO @CongVan VALUES(5,'286/CD-NHNO')
INSERT INTO @CongVan VALUES(6,'388/QD/NHNO-TP-HCNS')
SELECT * FROM @User
SELECT * FROM @CongVan
SELECT ROW_NUMBER() OVER(ORDER BY SoCV DESC) id,
STUFF((SELECT '|' + UserName FROM
(
SELECT UserName,SoCV
FROM @User u,@CongVan c
WHERE u.idCV = c.idCV
) as childTable
WHERE mainTable.SoCV= childTable.SoCV
FOR XML PATH('')), 1, 1, '') AS ten,
mainTable.SoCV,
mainTable.[Status]
FROM
(
SELECT UserName,SoCV,[Status]
FROM @User u,@CongVan c
WHERE u.idCV = c.idCV
) as mainTable
GROUP BY mainTable.SoCV,[Status]
Screenshot