By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table terms(termid int, termname varchar(20));
insert into terms values (1, 'term1'), (2, 'term2'), (3, 'term3'), (4, 'term4');
4 rows affected
select
termid,
string_agg(termname, ' and ') over(order by termid) termname
from terms
Msg 4113 Level 15 State 1 Line 3
The function 'string_agg' is not a valid windowing function, and cannot be used with the OVER clause.
with
data as (
select t.*, row_number() over(order by termid) rn from terms t
),
cte as (
select termid, cast(termname as varchar(max)) termname, rn
from data
where rn = 1
union all
select d.termid, c.termname + ' and ' + d.termname, d.rn
from cte c
inner join data d on d.rn = c.rn + 1
)
select termid, termname from cte
| termid | termname |
|---|---|
| 1 | term1 |
| 2 | term1 and term2 |
| 3 | term1 and term2 and term3 |
| 4 | term1 and term2 and term3 and term4 |