select 
  distinct CCTP.C5NCCT AS Numéro, 
  (
    SELECT 
      STUFF(
        (
          SELECT 
            ',' + etm.Spécialité AS Spécialité 
          FROM 
            (
              select 
                distinct CCTP.C5NCCT, 
                cast(
                  CCTP.CBCSPE as nvarchar(100)
                ) as Spécialitéfrom FIC.CCTP 
                LEFT JOIN FIC.CCTSPEP CCTSPEP ON CCTSPEP.CBNCCT = CCTP.C5NCCT
            ) etm 
          WHERE 
            etm.C5NCCT = FIC.CCTP.C5NCCT 
          ORDER BY 
            etm.C5NCCT FOR XML PATH('')
        ), 
        1, 
        1, 
        ''
      ) AS expr
  ) Spécialité