/*-----Create Sample Table-----*/
SELECT * INTO #Temp_Source
FROM
(
select '20100904001' [NIK],'Alfa Omega' [Nama]
union all
select '20100904001' [NIK],'Alfa O.' [Nama]
union all
select '20100904001' [NIK],'Alfa O. C' [Nama]
union all
select '20101004002' [NIK],'Beta E.' [Nama]
union all
select '20101004002' [NIK],'Beta Echo' [Nama]
) N
SELECT * FROM #Temp_Source
Results :
Query :
/*-----Query for Distinct a Grouping Data-----*/
SELECT DISTINCT NIK,New_Nama FROM
(
SELECT NIK,Nama,
CASE WHEN NIK IN
(select NIK from #Temp_Source where NIK = A.NIK group by NIK )
THEN
(select TOP 1 Nama from #Temp_Source where NIK = A.NIK )
END New_Nama
FROM #Temp_Source A
) AS Temp_1
Results: