Concatenating the records and use appropriate separator

If we have 4 records, we want to concatenate all 4 records and the last separator should be "&" symbol. We can achieve this by using row_number() function and then decide whether the separator is "," or "&".

For example:

declare @s nvarchar(max)

create table #tb1 (
    email nvarchar(255)
)
insert into #tb1 values ('a@a.com');
insert into #tb1 values ('b@a.com');
insert into #tb1 values ('c@a.com');
insert into #tb1 values ('d@a.com');

select
    @s = coalesce(@s
        + case when rowidx < cnt then ',' else ' & ' end
        + email, email)
from (
    select
        rowidx=row_number() over( order by email )
        , email
        , cnt = (select count(*) from tb1)
    from #tb1
) as a

select @s

drop table #tb1