What's the best way to create a string of childIDs for each parentID in SQL, while excluding the current childID from the aggregate

Question

Data

DROP TABLE IF EXISTS cats;

CREATE TABLE cats
(
    litterID int NOT NULL,
    catID int NOT null,  --row uniquifier
    catFirst varchar(30),
    catLast varchar(30),
    catSize int
);

INSERT INTO cats (litterID,catID,catFirst,catLast,catSize)
VALUES
('20020314','20262607','Scat','Meow','8'),
('20020314','20264036','Paws','Furbs','4'),
('20020314','20264267','Alexander','Meow','2');

Problem

For each catID create a string of their littermates (this is the hard part ...removing them from the set of members)

Expected Output

catID     catFirst     litterID  littermates
--------  ---------    --------  -----------------------------------------------
20262607  Scat         20020314  "Paws Furbs Size 4; Alexander Meow Size 2"
20264036  Paws         20020314  "Scat Meow Size 8; Alexander Meow Size 2"
20264267  Alexander    20020314  "Scat Meow Size 8; Paws Furbs Size 4"

I've tried EXCEPT query, CURSOR, WHILE. I think the solution lay in CROSS APPLY.

Answer 1

You want to list all other cats that belong to the same litter on each row.

One option uses a correlated subquery, or apply:

select c.*, c1.*
from cats c
cross apply (
    select 
        string_agg(concat_ws(' ', c1.catFirst, c1.catLast, 'Size', c1.catSize), '; ')
            within group(order by c1.catID) littermates
    from cats c1
    where c1.litterID = c.litterID and c1.catID <> c.catID
) c1

For each row in the outer query, what apply does is re-open the same table, then look for rows that have the same litterID and a different catID - then string_agg() aggregates the names and size into a scalar value.

litterID	catID	catFirst	catLast	catSize	littermates
20020314	20262607	Scat	Meow	8	Paws Furbs Size 4; Alexander Meow Size 2
20020314	20264036	Paws	Furbs	4	Scat Meow Size 8; Alexander Meow Size 2
20020314	20264267	Alexander	Meow	2	Scat Meow Size 8; Paws Furbs Size 4

fiddle