How to get parts have duplicate on code type and different on code ?

Question

I work on sql server 2012 I need to make select query display parts have duplicate on code type and different on code

CREATE TABLE #Trades  
(  
PartId int,  
CodeTypeId int,  
Code Int  
)  
insert into #Trades(PartId,CodeTypeId,Code)  
values  
(1215,1220,250),  
(1215,1220,110),  
(1350,1220,330),  
(1350,1220,900),  
(4521,2500,700),  
(4521,2500,800),  
(4521,2500,950),  
(3500,2900,230),  
(3500,2900,230),  
(5400,1220,230),  
(5400,1220,230)  

expected result

expected result text as image above

PartId	CodeTypeId	Code  
1215	1220	250  
1215	1220	110  
1350	1220	330  
1350	1220	900  
4521	2500	700  
4521	2500	800  
4521	2500	950  

Answer 1

Hi @ahmed salah ，

Please refer below:

select * from  #Trades  
where PartId in (  
select PartId from #Trades  
group by PartId,CodeTypeId  
having count(distinct code) >1)  

OR

;with cte as (  
select PartId,CodeTypeId from #Trades  
group by PartId,CodeTypeId  
having count(distinct code) >1)  
select a.*   
from #Trades a  
inner join cte b   
on a.PartId=b.PartId and a.CodeTypeId=b.CodeTypeId  

Output:

PartId	CodeTypeId	Code  
1215	1220	250  
1215	1220	110  
1350	1220	330  
1350	1220	900  
4521	2500	700  
4521	2500	800  
4521	2500	950  

If both of above are not working, please provide more sample data and expected output.

Best regards,
Melissa

---

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.