Select query to remove duplicate records of each group in SQL Server

Dear @babu935,

 refer below query , it will solved your problem.

SELECT PatientID, PatientName,
STUFF((SELECT ', ' + CAST(Location AS VARCHAR(10)) [text()] FROM TEST WHERE PatientID = T1.PatientID FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Location,
Age FROM TEST T1 GROUP BY PatientID, PatientName,Age order by PatientID

Hi babu935,

Refer below sample test query.

SQL

DECLARE @Test AS TABLE(PatientID INT,PatientName CHAR(20),Location CHAR(10),Age INT)
INSERT INTO @Test VALUES(1,'Satyam','CA',28)
INSERT INTO @Test VALUES(1,'Satyam','TX',28)
INSERT INTO @Test VALUES(2,'Sandeep','FL',32)
INSERT INTO @Test VALUES(2,'Sandeep','NV',32)
INSERT INTO @Test VALUES(3,'Suman','NV',52)

SELECT PatientID, PatientName,Location,Age 
FROM (
		SELECT ROW_NUMBER() OVER(Partition BY PatientID ORDER BY PatientID DESC) AS RowNumber,* 
		FROM @Test
	) rs 
WHERE RowNumber = 1

Output