Hi @ahmed salah ,
Please refer below and check whether it is working:
;with cte as (
select *,ROW_NUMBER() over (order by partid) rn from #codes
where PartId=1250)
,cte2 as (
select partid,CodeTypeId,floor((rn- 1)/2)+1 as groupid
from cte)
,cte3 as (
select distinct a.PartId,b.CodeTypeId,b.groupid from #codes a
cross apply (select CodeTypeId,groupid from cte2) b)
,cte4 as (
select a.*,b.PartId PartId1,b.Partlevel from cte3 a
left join ( select * from #codes where partlevel=0) b
on a.PartId=b.PartId and a.CodeTypeId=b.CodeTypeId )
,cte5 as (
select PartId,groupid from cte4
where PartId1 is null
group by PartId,groupid
having count(*)=1)
select distinct a.PartId,c.CodeTypeId,c.Partlevel
from cte5 a
inner join cte3 b on a.PartId=b.PartId and a.groupid=b.groupid
left join cte4 c on a.PartId=c.PartId and a.groupid=c.groupid
where PartId1 is not null
order by PartId
Output:
PartId CodeTypeId Partlevel
1900 1273200 0
2200 194480 0
3400 1273200 0
3900 1273200 0
5020 194480 0
8900 194480 0
9200 194480 0
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.