SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select *
into t
from (values  (1, 1, 'William'),
              (2, 1, 'James'),
              (3, 1, 'Mason'),
              (4, 1, 'Ella'),
              (5, 1, 'Jackson'),
              (6, 1, 'Evelyn'),
              (7, 2, 'Colton'),
              (8, 2, 'Robert'),
              (9, 3, 'Austin'),
              (10, 3, 'Cooper'),
              (11, 3, 'Parker'),
              (12, 4, 'Daisy'),
              (13, 5, 'Edward'),
              (14, 5, 'River')
) v(id, HouseId, Name)
14 rows affected

select houseid,
       (case when count(*) = 1 then max(name)
             else concat(string_agg(case when id <> max_id then name end, ', ') within group (order by id),
                         ' and ',
                         max(case when id = max_id then name end)
                        )
        end) as names
from (select t.*, max(id) over (partition by houseid) as max_id
      from t
     ) t
group by houseid


houseid
names


1
William, James, Mason, Ella, Jackson and Evelyn

2
Colton and Robert

3
Austin, Cooper and Parker

4
Daisy

5
Edward and River


Warning: Null value is eliminated by an aggregate or other SET operation.


select houseid,
       (case when count(*) = 1 then max(name)
             else concat(string_agg(case when name <> max_name then name end, ', ') within group (order by id),
                         ' and ',
                         max(case when name = max_name then name end)
                        )
        end) as names
from (select t.*, max(name) over (partition by houseid) as max_name
      from t
     ) t
group by houseid


houseid
names


1
James, Mason, Ella, Jackson, Evelyn and William

2
Colton and Robert

3
Austin, Cooper and Parker

4
Daisy

5
Edward and River


Warning: Null value is eliminated by an aggregate or other SET operation.

houseid	names
1	William, James, Mason, Ella, Jackson and Evelyn
2	Colton and Robert
3	Austin, Cooper and Parker
4	Daisy
5	Edward and River

houseid	names
1	James, Mason, Ella, Jackson, Evelyn and William
2	Colton and Robert
3	Austin, Cooper and Parker
4	Daisy
5	Edward and River