How to modify query below to accept also different on value?

Transact-SQL
,
1

I work on sql server 2012 i face issue i need to get partid have same code type id
but have different code and different value

 create table #tradecode
 (
 PartId int,
 codetypeId int,
 code nvarchar(20),
 zvalue nvarchar(20)
 )
 insert into #tradecode(PartID,CodeTypeId,Code,Zvalue)
 values
 (717286,849774,'8532240000','Multilayer'),
 (717286,849774,'8532240022','Multilayer'),
 (717286,849774,'8532240022','Chip'),
    
 (7172899,849777,'8532240033','Multilayer'),
 (7172899,849777,'8532240033','silicon'),
 (7172899,849777,'8532240033','Multilayer')

expected result

 PartId    codetypeId    code    zvalue
 717286    849774    8532240000    Multilayer
 717286    849774    8532240022    Multilayer
 717286    849774    8532240022    Chip

so partid 717286 have code type 849774 and have different on code as it have two code
8532240000 and 8532240022 and different value as chip and Multilayer
for same code
because 8532240022 have two values (Multilayer,Chip)

2

What have you tried?

3

select partid,codetypeid,count(1) from #tradecode
group by partid,codetypeid
having count(distinct code ) >1

but also i need distinct zvalue

4

one way

 select tc.*
  from #tradecode tc
  join (
		 select PartID
		   from #tradecode
		   group by PartId
		   having count(distinct Code) > 1
   ) code on tc.PartId = code.PartId
  join (
		 select PartID
		   from #tradecode
		   group by PartId
		   having count(distinct Zvalue) > 1
   ) Zvalue on tc.PartId = Zvalue.PartId
5

another approach

 ;with multi_Code
 as
 (
	select PartID
	from #tradecode
	group by PartId
	having count(distinct Code) > 1
 ), 
 multi_Zvalue AS
(
  select tc.PartID
	from #tradecode tc
	join multi_Code mc on tc.PartId = mc.PartId
	group by tc.PartId
	having count(distinct Zvalue) > 1
)
SELECT tc.*
   from #tradecode tc
   join multi_Zvalue mc on tc.PartId = mc.PartId
6

another way

;with multi_Code
 as
 (
	select PartID
	from #tradecode
	group by PartId
	having count(distinct Code) > 1
 ), multi_Zvalue AS
(
  select PartID
	from #tradecode tc
	group by PartId
	having count(distinct Zvalue) > 1
)
SELECT tc.*
   from #tradecode tc
   join multi_Code mc on tc.PartId = mc.PartId
   join multi_Zvalue mz  on tc.PartId = mz.PartId

or

select * 
 from #tradecode p
 where exists (
select PartId
  from #tradecode c
 where p.PartId = c.PartId
group by PartId
having count(distinct Code) > 1 
    and count(distinct Zvalue) > 1
)