Concat string Example using CTE

WITH cte(Centerid,PatientId,patientName,RefDocName,PhoneNo ,visitid,VisitDate,StudyName,StudyState) as (
  Select
       P.CenterId,
       p.externalID as PatientId,
        P.FirstName as patientName,
        R.FirstName as RefDocName,
        P.Phone1 as PhoneNo,
        V.visitid,
        V.VisitDate,
        bm.billingname as StudyName,
        CASE when s.StudyState=4 THEN 'Inprogress'
             when s.StudyState=3 THEN 'Completed' End as StudyState
  from TblPatient
 
  as P JOIN TblVisits as V On V.Patientid=P.patientid and V.CenterId=P.CenterId
  JOIN TblStudy as S On S.Visitid=V.visitid and S.Centerid=V.Centerid
  JOIN TblBillingMaster as BM On BM.Billingid=S.Studytypeid and Bm.CenterId=S.CenterId
  JOIN tblReferralDoctor as R ON R.ReferalID=v.ReferalID and R.CenterId=V.CenterId
 
  Where P.Obsolete=0 and V.Obsolete=0 AND S.StudyState In(3,4) and bm.ModalityTypeID!=12 )

  

SELECT CenterId,PatientId,patientName,RefDocName,PhoneNo,visitid,VisitDate,StudyState as Status, STUFF((SELECT ',' + b.StudyName from cte b Where a.Centerid=B.Centerid and a.visitid=b.visitid  FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,1, '') AS StudyName
FROM cte a
    GROUP BY    a.CenterId,a.PatientId,a.patientName,a.RefDocName,a.PhoneNo,a.visitid,a.VisitDate,a.StudyState
    
 

Delete Duplicate Rows Example

Delete A FROM (
     SELECT ROW_NUMBER()OVER(PARTITION BY PatientId ORDER BY LASTMODIFIEDDATE   DESC) RANK1,PatientId
     FROM TblPatient WHERE CenterId = 10 ) A  WHERE RANK1 > 1