Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have a table with materials information where one material has from one to many constituents.

The table looks like 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, I can have different material ID's with the same constituents (both ID's and weight percent), but also the same constituent id with the same weight percent can be in multiple materials.

I need to find the material ID's that have exactly the same amount of constituents, same constituents id's and same weight percent (in the example of data that will be material ID 1 and 3) What would be great is to have the output like:

ID Duplicate ID's
1 1,3
2 15,25
....

Just to clarify the question: I have several thousands of materials and it won't help me if I get just the id's of duplicate rows - I would like to see if it is possible to get the groups of duplicate material id's in the same row or field.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Build a XML string in a CTE that contains all constituents and use that string to figure out what materials is 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 into 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 as
(
  select M1.material_id,
        (
        select M2.constituent_id as I,
                M2.constituent_wt_pct as P
        from Materials as M2
        where M1.material_id = M2.material_id
        order by M2.constituent_id,
                 M2.material_id
        for xml path('')
        ) as constituents
  from Materials as M1
  group by M1.material_id
)
select row_number() over(order by 1/0) as ID,
       stuff((
       select ','+cast(C2.material_id as varchar(10))
       from C as C2
       where C1.constituents = C2.constituents
       for xml path('')
       ), 1, 1, '') as MaterialIDs
from C as C1
group by C1.constituents
having count(*) > 1

Results:

| ID | MATERIALIDS |
--------------------
|  1 |         1,3 |
share|improve this answer

Well you can use this to get the duplicate value

Select EMP_NAME as NameT,count(EMP_NAME) as DuplicateValCount From dbo.Emp_test group by Emp_name having count(EMP_NAME) > 1

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.12.20.2132\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": [" 1\n\nResults:\n\n| ID | MATERIALIDS |\n--------------------\n|  1 |         1,3 |\nshare|improve this answer\n\nWell you can use this to get the duplicate value\n\nSelect EMP_NAME as NameT,count(EMP_NAME) as DuplicateValCount From dbo.Emp_test group by Emp_name having count(EMP_NAME) > 1\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}