Copy Sr number if email match

Question

I am working on a query in SQL SERVER 2017. My table has 3 columns:Sr#,Email,Duplicate#.

The end goal is to copy the serial number from Sr# column to Duplicate# if the email addresses repeat. For example, aahil.mawani3@gmail.com email has 3 entries in the table with Sr# 4493, 4117, 4366. So as a result I would need Sr# 4117,4366 to get copied into the Duplicate# column. A sample of this result is shown below:

Answer 1

Couple things here. First, don't use characters like hash symbols in your column names. It means you have to always quote those column names, and it's completely unnecessary.

Second, I sincerely hope this is just some sort of weird report formatting you're doing because you physically can't do it on the front end. If this is a persisted storage format, you need to rethink your design.

That out of the way, you just need to string_agg your fields together. The oddity in your results however is that you want to concatenate all the SrNo values except for the SrNo on the current row. This means you're going to have to join your data to itself on the email where the SrNo values differ.

I'd recommend you NOT store this in a dedicated field (i.e. doing an update) and that you just project this when you're querying the data out. But regardless, here's how you could do it both ways. Note I'm assuming SrNo is a unique key on the table.

drop table if exists #data
;with a (SrNo, Email, DupNo) as
(
    select 4117, 'a@b.c', cast(null as varchar(max)) union all
    select 4366, 'a@b.c', null union all
    select 4493, 'a@b.c', null union all
    select 90210, 'z@x.y', null union all
    select 1234, 'z@x.y', null union all
    select 0, 'q@r.s', null
)
select *
into #data
from a 

-- To just select 
;with dups as
(
    select
        AnchorSRNo = a.SrNo,
        Email = a.Email,
        DupSrNo = b.SrNo
    from #data a
    inner join #data b
        on a.Email = b.Email
            and a.SrNo != b.SrNo
)
select 
    o.*,
    DupNos = 
    (
        select string_agg(DupSrNo, ',')
        from dups i
        where o.SrNo = i.AnchorSrNo
    )
from #data o

-- To perform an update
;with dups as
(
    select
        AnchorSRNo = a.SrNo,
        Email = a.Email,
        DupSrNo = b.SrNo
    from #data a
    inner join #data b
        on a.Email = b.Email
            and a.SrNo != b.SrNo
)
update t
set DupNo = (
    select string_agg(DupSrNo, ',')
    from dups i
    where t.SrNo = i.AnchorSrNo
)
from #data t
inner join dups d
    on t.SrNo = d.AnchorSrNo

db<>fiddle