can be further simplified
; with mycte as
(SELECT a.MemberID
      , STUFF(
      (SELECT ',' + p.ProgramName
       From MemberProgram mp
       Inner join Program p on p.programid = mp.programid
       WHERE mp.Memberid = a.Memberid
       Order By mp.isprimary DESC
       FOR XML PATH (''))
       , 1, 1, '') AS ProgramList
FROM Member AS a
)
select * from mycte