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.