Quick window function based solution with xml path concatenation, should get you passed this hurdle.

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.data_set') IS NOT NULL DROP TABLE dbo.data_set;

create table dbo.data_set (id int primary key, col1 varchar(10));

go

insert into dbo.data_set values (1,'a'), (2,'b'),(3,'c'),(4,'d'),(5,'a'),(6,'b'),(7,'e'),(8,'f'),(9,'a'),(10,'a')

;WITH BASE_DATA AS

(

select

DS.id

,ROW_NUMBER() OVER

(

PARTITION BY DS.col1

ORDER BY DS.id ASC

) AS Parent_RID

,DS.col1 AS Col_val

,COUNT(DS.id) OVER

(

PARTITION BY DS.col1

) AS [count]

from dbo.data_set DS

)

SELECT

BD.id

,BD.Col_val

,STUFF( (SELECT CONCAT(CHAR(44),BS.id)

FROM BASE_DATA BS

WHERE BD.Col_val = BS.Col_val

FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(100)'),1,1,'') AS Child_ids

,BD.[count]

FROM BASE_DATA BD

WHERE BD.Parent_RID = 1;

Results

id Col_val Child_ids count

----------- ---------- ------------ ---------

1 a 1,5,9,10 4

2 b 2,6 2

3 c 3 1

4 d 4 1

7 e 7 1

8 f 8 1