CODEWITHSUNDEEP
sql-server-2008-r2ssrs-2008-r2
raj
raj

Reputation: 143

Report duplicate data

create table dupt(cat varchar(10), num int)

insert dupt(cat,num) values ('A',1),('A',2),('A',3),
                            ('B',1),('B',2),
                            ('C',1),('C',2), ('C',3),
                            ('D',1),('D',2), ('D',4),
                            ('E',1),('E',2),
                            ('F',1),('F',2)

I need to create a report which finds out duplicate data. From the sample data above, report needs to show that data for cat A is duplicated by cat C (notice the num value and no. of records) and cat B is duplicated by cat E and F. What is the best way to show that?

Example output

-------------
|cat | dupby|
-------------
| A  |  C   |
| B  | E, F |
-------------

Upvotes: 2

Views: 108

Answers (1)

SqlZim
SqlZim

Reputation: 38023

Updated: switched to traditional set matching using common table expression and the stuff() with select ... for xml path ('') method of string concatenation only on the final results:

;with cte as (
  select *
    , cnt = count(*) over (partition by cat)
  from t
)
, duplicates as (
  select 
        x.cat
      , dup_cat = x2.cat
  from cte as x 
    inner join cte as x2
       on x.cat < x2.cat
      and x.num = x2.num
      and x.cnt = x2.cnt 
  group by x.cat, x2.cat, x.cnt
  having count(*) = x.cnt
)
select 
    d.cat
  , dupby = stuff((
        select ', '+i.dup_cat
        from duplicates i
        where i.cat = d.cat
        for xml path (''), type).value('.','varchar(8000)')
     ,1,2,'')
from duplicates d
where not exists (
    select 1
    from duplicates i
    where d.cat = i.dup_cat
    )
group by d.cat

rextester demo: http://rextester.com/KHAG98718

returns:

+-----+-------+
| cat | dupby |
+-----+-------+
| A   | C     |
| B   | E, F  |
+-----+-------+

Upvotes: 1

Related Questions