How to check multiple rows

Question

Hi, I want to know the duplicate rows (if ALL column's data are same). There is no key for joining. I want to know how many rows are duplicate if exist. If there is no duplicate row then Result should show 'Original'. If any row is duplicate then should show ' Duplicate'. If Duplicate of same row is more than one then should show 'Duplicate1'...........'Duplicate2' etc. I don't want to delete the Duplicate rows. Just want to see the data.

drop table #Dup
Create table #Dup (TCode char(10), Fcode char(10), PCode char(10), TpCode char(10), CCode char(20), CityCode char(20),ACode int, ICode char(10))
Insert into #Dup values ('1000','AA','A1','U1','GH','Country',0,'N')
Insert into #Dup values ('1000','AA','A1','U1','GH','Country',0,'N')

Insert into #Dup values ('1000','AA','A1','A1','GH','Country',0,'N')
Insert into #Dup values ('1000','AA','A1','A1','GH','Country',0,'N')
Insert into #Dup values ('1000','AA','A1','A1','GH','Country',0,'N')

Insert into #Dup values ('5000','AA','A1','A1','AA','Country',0,'N')
Insert into #Dup values ('5000','AA','A1','A1','AA','Country',0,'N')

Insert into #Dup values ('7000','AA','A1','Z1','AA','Country',0,'Y')
Insert into #Dup values ('7000','AA','A1','Z1','AA','Country',0,'Y')
Insert into #Dup values ('7000','AA','A1','Z1','AA','Country',0,'Y')

Insert into #Dup values ('8000','ZZ','A1','Z1','AA','Country',0,'Y')

Answer 1

Hi @Sumit ，

Please also check another method：

        ;with cte as  
        (select *,row_number() over(partition by TCode,Fcode,PCode,TpCode,CCode,CityCode,ACode,ICode order by TCode) rn  
         from #Dup)  
        ,cte2 as  
        (select *,max(rn) over(partition by TCode,Fcode,PCode,TpCode,CCode,CityCode,ACode,ICode order by TCode) mm   
        from cte )  
          
        select TCode, Fcode, PCode, TpCode, CCode, CityCode, ACode, ICode,  
               case  when rn=1 then 'Original'  
                     when mm=2 then 'Duplicate'   
               else concat('Duplicate-', rn-1) end Result  
        from cte2        

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check this possibility:

;
with CTE as
(
    select *, 
        row_number() over (partition by TCode, Fcode, PCode, TpCode, CCode, CityCode, ACode, ICode order by @@spid) as rn,
        count(*) over (partition by TCode, Fcode, PCode, TpCode, CCode, CityCode, ACode, ICode ) as cnt
    from #Dup
)
select TCode, Fcode, PCode, TpCode, CCode, CityCode, ACode, ICode,
    case rn when 1 then 'Original'
    else case cnt
        when 2 then 'Duplicate' 
        else concat('Duplicate-', rn - 1) end end as Result
from CTE
order by TCode, Fcode, PCode, TpCode, CCode, CityCode, ACode, ICode, rn