/*-----Create Sample Table-----*/
SELECT * INTO #Temp_Source
FROM
(
select '01' [Numb],'Y' [Status]
union all
select '01' [Numb],'Y' [Status]
union all
select '02' [Numb],'Y' [Status]
union all
select '02' [Numb],NULL [Status]
union all
select '02' [Numb],'N' [Status]
union all
select '02' [Numb],'N' [Status]
) N
SELECT * FROM #Temp_Source
Results :
Query :
/*-----Query for Distinct a Grouping Data-----*/
SELECT DISTINCT Numb,New_Status FROM
(
SELECT Numb,Status,
CASE WHEN Numb IN
(select Numb from #Temp_Source where Status = 'N' group by Numb)
THEN 'N'
ELSE 'Y'
END New_Status
FROM #Temp_Source
) AS Temp_1
Results :
