Find duplicate groups of rows in SQL Server -

i have table materials information 1 material has 1 many constituents.

the table looks this:

material_id contstiuent_id constituent_wt_pct    1             1              10.5    1             2              89.5    2             1              10.5    2             5              15.5    2             7              74    3             1              10.5    3             2              89.5 

generally, can have different material id's same constituents (both id's , weight percent), same constituent id same weight percent can in multiple materials.

i need find material id's have same amount of constituents, same constituents id's , same weight percent (in example of data material id 1 , 3) great have output like:

id duplicate id's
1 1,3
2 15,25
....

just clarify question: have several thousands of materials , won't me if id's of duplicate rows - see if possible groups of duplicate material id's in same row or field.

build xml string in cte contains constituents , use string figure out materials duplicate.

sql fiddle

ms sql server 2008 schema setup:

create table materials (   material_id int,    constituent_id int,    constituent_wt_pct decimal(10, 2) );   insert materials values (1, 1, 10.5), (1, 2, 89.5), (2, 1, 10.5), (2, 5, 15.5), (2, 7, 74), (3, 1, 10.5), (3, 2, 89.5); 

query 1:

with c (   select m1.material_id,         (         select m2.constituent_id i,                 m2.constituent_wt_pct p         materials m2         m1.material_id = m2.material_id         order m2.constituent_id,                  m2.material_id         xml path('')         ) constituents   materials m1   group m1.material_id ) select row_number() over(order 1/0) id,        stuff((        select ','+cast(c2.material_id varchar(10))        c c2        c1.constituents = c2.constituents        xml path('')        ), 1, 1, '') materialids c c1 group c1.constituents having count(*) > 1 

results:

| id | materialids | -------------------- |  1 |         1,3 |