CODEWITHSUNDEEP
sqlsql-server
user2718203
user2718203

Reputation: 3

remove duplicates in column TSQL

wondering anyone could help me please. I have date output from a Stored Procedure something like this:

Year    AccountName Certs   MoreCerts
2015    Rosy        3650097   444455
2015    Rosy        3650097   5645665
2015    Rosy        3650097    4544
2015    Rosy        3650097   33423
2015    Rosy        3650097    232454
2014    Rosy        112379     6767878
2014    Rosy    112379  68878
2014    Rosy    112379  567
2014    Rosy    112379  567
2014    Rosy    112379  4567
2014    Rosy    112379  345667
2015    John    12000   23456
2015    John    12000   23345
2015    John    12000   2111
2015    John    12000   456
2014    John    50000   887
2014    John    50000   43432
2014    John    50000   2333

and I would like to have like this i.e remove duplicate values from Certs columns while retain everything else in table as it is

Year    AccountName Certs        MoreCerts
2015    Rosy         3650097      444455
2015    Rosy                      5645665
2015    Rosy                      4544
2015    Rosy                      33423
2015    Rosy                      232454
2014    Rosy         112379       6767878
2014    Rosy                      68878
2014    Rosy                       567
2014    Rosy                       567
2014    Rosy                       4567
2014    Rosy                       345667
2015    John         12000         23456
2015    John                       23345
2015    John                       2111
2015    John                       456
2014    John         50000         887
2014    John                       43432
2014    John                       2333

tried row_number and sub select etc. but cant figure out. any help pls?

Upvotes: 0

Views: 55

Answers (1)

Blorgbeard
Blorgbeard

Reputation: 103437

Something like this should work:

select
    Year, AccountName, case when rownum=1 then Certs else '' end as Certs, MoreCerts
from (
    select *, row_number() over (partition by Certs order by MoreCerts) [rownum]
    from your_table
) x
order by x.Certs, rownum

Depending on your data, you may wish to add columns to the partition-by and/or change the order-by for row_number().

Upvotes: 2

Related Questions