SELECT cc.Surname
	,cc.ContactCode as [Contact Code]
	,ge.ObsResearchOther as [Other Observational Research]
    	,gx.InterResearchOther as [Other Interventional Research]
    	,gz.DiseaseAreaOther as [Other Disease Area]
    	,ga.InterestGroupADesc as [Area of Interest]
    	,gb.InterestGroupBListADesc as [Observational Research]
    	,gc.InterestGroupBListBDesc as [Interventional Research]
    	,gd.InterestGroupBListCDesc as [Disease Area]

 FROM  dbo.aspnet_cprdContacts                 AS cc

 LEFT  OUTER JOIN
       (
       SELECT ai .ContactCode
            , iga.InterestGroupADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ai.ContactCode
                           ORDER BY iga.InterestGroupADesc
                     ) AS rnum
        FROM  dbo.aspnet_AreaofInterest        AS ai
        INNER JOIN
              dbo.aspnet_InterestGroupA        AS iga
          ON  ai.InterestCode = iga.InterestGroupAID
       )                                       AS ga

 FULL  OUTER JOIN
       (
       SELECT ab.ContactCode
            , igb.InterestGroupBListADesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ab.ContactCode
                           ORDER BY igb.InterestGroupBListADesc
                     ) AS rnum
        FROM  dbo.aspnet_ObservationalResearch AS ab
        INNER JOIN
              dbo.aspnet_InterestGroupBListA   AS igb
          ON  ab.ObsResearchCode = igb.InterestGroupBListAID
       )                                       AS gb
   ON  gb.ContactCode = ga.ContactCode
   AND gb.rnum        = ga.rnum

FULL  OUTER JOIN
       (
       SELECT ax.ContactCode
            , igx.InterResearchOther
            , ROW_NUMBER()
                 OVER( PARTITION BY ax.ContactCode
                           ORDER BY igx.InterResearchOther
                     ) AS rnum
        FROM  dbo.aspnet_InterResearchOther  AS ax
        INNER JOIN
            dbo.aspnet_cprdContacts AS igx
          ON  ax.ContactCode = igx.ContactCode
       )                                       AS gx
   ON  gx.ContactCode = gb.ContactCode
   AND gx.rnum        = gb.rnum

FULL  OUTER JOIN
       (
       SELECT az.ContactCode
            , igz.DiseaseAreaOther
            , ROW_NUMBER()
                 OVER( PARTITION BY az.ContactCode
                           ORDER BY igz.DiseaseAreaOther
                     ) AS rnum
        FROM  dbo.aspnet_DiseaseAreaOther  AS az
        INNER JOIN
            dbo.aspnet_cprdContacts AS igz
          ON  az.ContactCode = igz.ContactCode
       )                                       AS gz
   ON  gz.ContactCode = gx.ContactCode
   AND gz.rnum        = gx.rnum
 
FULL  OUTER JOIN
       (
       SELECT ac .ContactCode
            , igc.InterestGroupBListBDesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ac.ContactCode
                           ORDER BY igc.InterestGroupBListBDesc
                     ) AS rnum
        FROM  dbo.aspnet_InterventionalResearch AS ac
        INNER JOIN
              dbo.aspnet_InterestGroupBListB   AS igc
          ON  ac.IntResearchCode = igc.InterestGroupBListBID
       )                                       AS gc
   ON  gc.ContactCode = gb.ContactCode
   AND gc.rnum        = gb.rnum

 
FULL  OUTER JOIN
       (
       SELECT ad.ContactCode
            , igd.InterestGroupBListCDesc
            , ROW_NUMBER()
                 OVER( PARTITION BY ad.ContactCode
                           ORDER BY igd.InterestGroupBListCDesc
                     ) AS rnum
        FROM  dbo.aspnet_DiseaseArea AS ad
        INNER JOIN
              dbo.aspnet_InterestGroupBListC   AS igd
          ON  ad.DiseaseAreaCode = igd.InterestGroupBListCID
       )                                       AS gd
   ON  gd.ContactCode = gc.ContactCode
   AND gd.rnum        = gc.rnum

FULL  OUTER JOIN
       (
       SELECT ae.ContactCode
            , ige.ObsResearchOther
            , ROW_NUMBER()
                 OVER( PARTITION BY ae.ContactCode
                           ORDER BY ige.ObsResearchOther
                     ) AS rnum
        FROM  dbo.aspnet_ObsResearchOther  AS ae
        INNER JOIN
            dbo.aspnet_cprdContacts AS ige
          ON  ae.ContactCode = ige.ContactCode
       )                                       AS ge
   ON  ge.ContactCode = gd.ContactCode
   AND ge.rnum        = gd.rnum

ON  cc.ContactCode = COALESCE(ge.ContactCode , gd.ContactCode)
where cc.Surname ='Mascrier'

where cc.Surname ='Mascrier'

order by cc.surname

SELECT *
FROM YourView
ORDER BY surname