select ID,Item1,Item2,Item3,Item4
into #Temp
from (
select ID='200910',Item1='A',Item2='B',Item3='C',Item4='T1'
union all
select ID='200910',Item1='',Item2='B',Item3='C' ,Item4='G2'
union all
select ID='200910',Item1='A',Item2='D',Item3='C' ,Item4='T3'
union all
select ID='200910',Item1='A',Item2='F',Item3='C' ,Item4='H4'
union all
select ID='200911',Item1='A',Item2='D',Item3='C' ,Item4='T5'
union all
select ID='200911',Item1='A',Item2='',Item3='E' ,Item4='T6'
union all
select ID='200911',Item1='',Item2='',Item3='C' ,Item4='T7'
) Tab
SELECT A.*
into #T1
from #Temp AS A
LEFT JOIN #Temp AS B
ON A.ID = B.ID
AND CASE WHEN ISNULL(NULLIF(A.Item1,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(A.Item2,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(A.Item3,''),'') = '' THEN 1 ELSE 0 END
< CASE WHEN ISNULL(NULLIF(B.Item1,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(B.Item2,''),'') = '' THEN 1 ELSE 0 END
+ CASE WHEN ISNULL(NULLIF(B.Item3,''),'') = '' THEN 1 ELSE 0 END
AND ISNULL(NULLIF(B.Item1,''),A.Item1) = A.Item1
AND ISNULL(NULLIF(B.Item2,''),A.Item2) = A.Item2
AND ISNULL(NULLIF(B.Item3,''),A.Item3) = A.Item3
WHERE B.ID IS NULL
select ID,Item1,Item2,Item3
into #T2
from (
select ID='200910',Item1='A',Item2='',Item3='C'
union all
select ID='200910',Item1='A',Item2='D',Item3='C'
union all
select ID='200911',Item1='A',Item2='H',Item3='C'
union all
select ID='200911',Item1='',Item2='',Item3='C'
) Tab
select * from #Temp
select * from #T1
select * from #T2
select A.*,B.* from #T2 A
left join #T1 B
ON A.ID=B.ID
AND ISNULL(NULLIF(B.Item1,''),A.Item1) = A.Item1
AND ISNULL(NULLIF(B.Item2,''),A.Item2) = A.Item2
AND
