SELECT o.OPERATIONALRISKASSESSMENTID AS [RISKID], o.RISKIDNUMBER AS [Risk Code], o.TITLE AS [Risk Title], DBO.FNLABELREPLACEMENT('Operational') AS [Risk Type], [Active?] = CASE o.[ACTIVE] WHEN 0 THEN 'No' ELSE 'Yes' END, s.[NAME] + '-' + s.POSITION AS [Responsible Person], o.DATEFIRSTRISKIDENTIFIED AS [Risk Identified], x.RISKRATINGRESULTREVISED as [Current Risk Assessment Risk Rating Value], x.RISKRATINGRESULTFUTURE as [Target Risk Assessment Risk Rating Value], x.RISKRATINGRESULTINITIAL as [Initial Risk Assessment Risk Rating Value], rtypr.RISKRATINGTYPENAME AS [Current Risk Assessment Risk Rating Description], rtypf.RISKRATINGTYPENAME AS [Target Risk Assessment Risk Rating Description], rtypi.RISKRATINGTYPENAME AS [Initial Risk Assessment Risk Rating Description], o.DATERISKLASTREVIEWED AS [Last Reviewed Date], rcat.RISKCATEGORY AS [Primary Risk Category], scat.SecRiskSubCategories AS [Secondary Risk Category], sper.SYSTEMPERIODID, (SELECT top 1 gcus.Causes FROM OperationalRiskAssessmentFieldsValue gcus INNER JOIN RISKASSESSMENTTYPE r ON r.RISKASSESSMENTTYPEID = gcus.RiskAssessmentTypeID INNER JOIN RISKASSESSMENTTYPELEVEL rt ON rt.RISKASSESSMENTTYPELEVELID = r.RISKASSESSMENTTYPELEVELID LEFT OUTER JOIN RiskCustomList cl ON cl.RiskCustomListID = gcus.BusinessProcessID WHERE rt.[LEVEL] = 'I' and gcus.OperationalRiskAssessmentID = o.OPERATIONALRISKASSESSMENTID ORDER BY gcus.LastChanged DESC) as [Causes] FROM OPERATIONALRISKASSESSMENT o LEFT OUTER JOIN CRITICALISSUE ci ON ci.CRITICALISSUEID = o.CRITICALISSUEID LEFT OUTER JOIN BUSINESSUNIT bu ON bu.BUSINESSUNITID = ci.BUSINESSUNITID LEFT OUTER JOIN STAFF s ON s.STAFFID = o.STAFFID INNER JOIN SYSTEMPERIOD sper ON s.SYSTEMPERIODID = sper.SYSTEMPERIODID LEFT OUTER JOIN VIEW_RiskRatingOperational x ON o.OPERATIONALRISKASSESSMENTID = x.RiskId LEFT OUTER JOIN RISKRATINGTYPE rtypi ON rtypi.RISKRATINGTYPEID = x.InitialRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypr ON rtypr.RISKRATINGTYPEID = x.RevisedRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypf ON rtypf.RISKRATINGTYPEID = x.FutureRatingId LEFT OUTER JOIN ( SELECT v.OPERATIONALRISKASSESSMENTID, r.RISKCATEGORY FROM OPERATIONALRISKASSESSMENT v LEFT OUTER JOIN OPERATIONALRISKASSESSMENTCATEGORY c ON v.OPERATIONALRISKASSESSMENTID = c.OPERATIONALRISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 1 ) rcat ON o.OPERATIONALRISKASSESSMENTID = rcat.OPERATIONALRISKASSESSMENTID LEFT OUTER JOIN ( SELECT mx.OPERATIONALRISKASSESSMENTID, STUFF( ( SELECT '; ' + r.RISKCATEGORY FROM OPERATIONALRISKASSESSMENT j LEFT OUTER JOIN OPERATIONALRISKASSESSMENTCATEGORY c ON j.OPERATIONALRISKASSESSMENTID = c.OPERATIONALRISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 0 AND j.OPERATIONALRISKASSESSMENTID = mx.OPERATIONALRISKASSESSMENTID AND r.ParentCategoryID IS NULL FOR XML PATH('') ), 1, 1, '' ) AS [SecRiskSubCategories] FROM OPERATIONALRISKASSESSMENT mx GROUP BY mx.OPERATIONALRISKASSESSMENTID ) scat ON scat.OPERATIONALRISKASSESSMENTID = o.OPERATIONALRISKASSESSMENTID union all SELECT o.PROJECTRISKASSESSMENTID AS [RISKID], o.RISKIDNUMBER AS [Risk Code], o.TITLE AS [Risk Title], DBO.FNLABELREPLACEMENT('Project') AS [Risk Type], [Active?] = CASE o.[ACTIVE] WHEN 0 THEN 'No' ELSE 'Yes' END, s.[NAME] + '-' + s.POSITION AS [Responsible Person], o.DATEFIRSTRISKIDENTIFIED AS [Risk Identified], x.RISKRATINGRESULTREVISED as [Current Risk Assessment Risk Rating Value], x.RISKRATINGRESULTFUTURE as [Target Risk Assessment Risk Rating Value], x.RISKRATINGRESULTINITIAL as [Initial Risk Assessment Risk Rating Value], rtypr.RISKRATINGTYPENAME AS [Current Risk Assessment Risk Rating Description], rtypf.RISKRATINGTYPENAME AS [Target Risk Assessment Risk Rating Description], rtypi.RISKRATINGTYPENAME AS [Initial Risk Assessment Risk Rating Description], o.DATERISKLASTREVIEWED AS [Last Reviewed Date], rcat.RISKCATEGORY AS [Primary Risk Category], sriskcat.PrimarySubCategories AS [Secondary Risk Category], sper.SYSTEMPERIODID, (SELECT TOP 1 gcus.Causes FROM ProjectRiskAssessmentFieldsValue gcus INNER JOIN RISKASSESSMENTTYPE r ON r.RISKASSESSMENTTYPEID = gcus.RiskAssessmentTypeID INNER JOIN RISKASSESSMENTTYPELEVEL rt ON rt.RISKASSESSMENTTYPELEVELID = r.RISKASSESSMENTTYPELEVELID LEFT OUTER JOIN RiskCustomList cl ON cl.RiskCustomListID = gcus.BusinessProcessID WHERE rt.[LEVEL] = 'I' and gcus.ProjectRiskAssessmentID = o.PROJECTRISKASSESSMENTID ORDER BY gcus.LastChanged DESC) as [Causes] FROM PROJECTRISKASSESSMENT o LEFT OUTER JOIN STAFF s ON s.STAFFID = o.STAFFID INNER JOIN SYSTEMPERIOD sper ON s.SYSTEMPERIODID = sper.SYSTEMPERIODID LEFT OUTER JOIN VIEW_RiskRatingPROJECT x ON o.PROJECTRISKASSESSMENTID = x.RiskId LEFT OUTER JOIN RISKRATINGTYPE rtypi ON rtypi.RISKRATINGTYPEID = x.InitialRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypr ON rtypr.RISKRATINGTYPEID = x.RevisedRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypf ON rtypf.RISKRATINGTYPEID = x.FutureRatingId LEFT OUTER JOIN ( SELECT p.PROJECTRISKASSESSMENTID, r.RISKCATEGORY FROM PROJECTRISKASSESSMENT p LEFT OUTER JOIN PROJECTRISKASSESSMENTCATEGORY c ON p.PROJECTRISKASSESSMENTID = c.PROJECTRISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 1 ) rcat ON o.PROJECTRISKASSESSMENTID = rcat.PROJECTRISKASSESSMENTID LEFT OUTER JOIN ( SELECT m.PROJECTRISKASSESSMENTID, STUFF( ( SELECT '; ' + r.RISKCATEGORY FROM PROJECTRISKASSESSMENT v LEFT OUTER JOIN PROJECTRISKASSESSMENTCATEGORY c ON v.PROJECTRISKASSESSMENTID = c.PROJECTRISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 0 AND v.PROJECTRISKASSESSMENTID = m.PROJECTRISKASSESSMENTID AND r.ParentCategoryID IS NULL FOR XML PATH('') ), 1, 1, '' ) AS [PrimarySubCategories] FROM PROJECTRISKASSESSMENT m GROUP BY m.PROJECTRISKASSESSMENTID ) sriskcat ON sriskcat.PROJECTRISKASSESSMENTID = o.PROJECTRISKASSESSMENTID union all SELECT o.STRATEGICRISKASSESSMENTID AS [RISKID], o.RISKIDNUMBER AS [Risk Code], o.TITLE AS [Risk Title], DBO.FNLABELREPLACEMENT('Strategic') AS [Risk Type], [Active?] = CASE o.[ACTIVE] WHEN 0 THEN 'No' ELSE 'Yes' END, s.[NAME] + '-' + s.POSITION AS [Responsible Person], o.DATEFIRSTRISKIDENTIFIED AS [Risk Identified], x.RISKRATINGRESULTREVISED as [Current Risk Assessment Risk Rating Value], x.RISKRATINGRESULTFUTURE as [Target Risk Assessment Risk Rating Value], x.RISKRATINGRESULTINITIAL as [Initial Risk Assessment Risk Rating Value], rtypr.RISKRATINGTYPENAME AS [Current Risk Assessment Risk Rating Description], rtypf.RISKRATINGTYPENAME AS [Target Risk Assessment Risk Rating Description], rtypi.RISKRATINGTYPENAME AS [Initial Risk Assessment Risk Rating Description], o.DATERISKLASTREVIEWED AS [Last Reviewed Date], rcat.RISKCATEGORY AS [Primary Risk Category], sriskcat.SecRiskCategories AS [Secondary Risk Category], sper.SYSTEMPERIODID, (SELECT TOP 1 gcus.Causes FROM StrategicRiskAssessmentFieldsValue gcus INNER JOIN RISKASSESSMENTTYPE r ON r.RISKASSESSMENTTYPEID = gcus.RiskAssessmentTypeID INNER JOIN RISKASSESSMENTTYPELEVEL rt ON rt.RISKASSESSMENTTYPELEVELID = r.RISKASSESSMENTTYPELEVELID LEFT OUTER JOIN RiskCustomList cl ON cl.RiskCustomListID = gcus.BusinessProcessID WHERE rt.[LEVEL] = 'I' and gcus.StrategicRiskAssessmentID = o.STRATEGICRISKASSESSMENTID ORDER BY gcus.LastChanged DESC) as [Causes] FROM STRATEGICRISKASSESSMENT o LEFT OUTER JOIN STAFF s ON s.STAFFID = o.STAFFID INNER JOIN SYSTEMPERIOD sper ON s.SYSTEMPERIODID = sper.SYSTEMPERIODID LEFT OUTER JOIN VIEW_RiskRatingStrategic x ON o.STRATEGICRISKASSESSMENTID = x.RiskId LEFT OUTER JOIN RISKRATINGTYPE rtypi ON rtypi.RISKRATINGTYPEID = x.InitialRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypr ON rtypr.RISKRATINGTYPEID = x.RevisedRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypf ON rtypf.RISKRATINGTYPEID = x.FutureRatingId LEFT OUTER JOIN ( SELECT s.STRATEGICRISKASSESSMENTID, r.RISKCATEGORY FROM STRATEGICRISKASSESSMENT s LEFT OUTER JOIN STRATEGICRISKASSESSMENTCATEGORY c ON s.STRATEGICRISKASSESSMENTID = c.STRATEGICRISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 1 ) rcat ON o.STRATEGICRISKASSESSMENTID = rcat.STRATEGICRISKASSESSMENTID LEFT OUTER JOIN ( SELECT m.STRATEGICRISKASSESSMENTID, STUFF( ( SELECT '; ' + r.RISKCATEGORY FROM STRATEGICRISKASSESSMENT v LEFT OUTER JOIN STRATEGICRISKASSESSMENTCATEGORY c ON v.STRATEGICRISKASSESSMENTID = c.STRATEGICRISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 0 AND v.STRATEGICRISKASSESSMENTID = m.STRATEGICRISKASSESSMENTID AND r.ParentCategoryID IS NULL FOR XML PATH('') ), 1, 1, '' ) AS [SecRiskCategories] FROM STRATEGICRISKASSESSMENT m GROUP BY m.STRATEGICRISKASSESSMENTID ) sriskcat ON sriskcat.STRATEGICRISKASSESSMENTID = o.STRATEGICRISKASSESSMENTID union all SELECT o.CORPORATERISKASSESSMENTID AS [RISKID], o.RISKIDNUMBER AS [Risk Code], o.TITLE AS [Risk Title], DBO.FNLABELREPLACEMENT('Corporate') AS [Risk Type], [Active?] = CASE o.[ACTIVE] WHEN 0 THEN 'No' ELSE 'Yes' END, s.[NAME] + '-' + s.POSITION AS [Responsible Person], o.DATEFIRSTRISKIDENTIFIED AS [Risk Identified], x.RISKRATINGRESULTREVISED as [Current Risk Assessment Risk Rating Value], x.RISKRATINGRESULTFUTURE as [Target Risk Assessment Risk Rating Value], x.RISKRATINGRESULTINITIAL as [Initial Risk Assessment Risk Rating Value], rtypr.RISKRATINGTYPENAME AS [Current Risk Assessment Risk Rating Description], rtypf.RISKRATINGTYPENAME AS [Target Risk Assessment Risk Rating Description], rtypi.RISKRATINGTYPENAME AS [Initial Risk Assessment Risk Rating Description], o.DATERISKLASTREVIEWED AS [Last Reviewed Date], rcat.RISKCATEGORY AS [Primary Risk Category], sriskcat.SecRiskCategories AS [Secondary Risk Category], sper.SYSTEMPERIODID, (SELECT TOP 1 gcus.Causes FROM CorporateRiskAssessmentFieldsValue gcus INNER JOIN RISKASSESSMENTTYPE r ON r.RISKASSESSMENTTYPEID = gcus.RiskAssessmentTypeID INNER JOIN RISKASSESSMENTTYPELEVEL rt ON rt.RISKASSESSMENTTYPELEVELID = r.RISKASSESSMENTTYPELEVELID LEFT OUTER JOIN RiskCustomList cl ON cl.RiskCustomListID = gcus.BusinessProcessID WHERE rt.[LEVEL] = 'I' and gcus.CorporateRiskAssessmentID = o.CORPORATERISKASSESSMENTID ORDER BY gcus.LastChanged DESC) as [Causes] FROM CORPORATERISKASSESSMENT o LEFT OUTER JOIN STAFF s ON s.STAFFID = o.STAFFID INNER JOIN SYSTEMPERIOD sper ON s.SYSTEMPERIODID = sper.SYSTEMPERIODID LEFT OUTER JOIN VIEW_RiskRatingCorporate x ON o.CORPORATERISKASSESSMENTID = x.RiskId LEFT OUTER JOIN RISKRATINGTYPE rtypi ON rtypi.RISKRATINGTYPEID = x.InitialRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypr ON rtypr.RISKRATINGTYPEID = x.RevisedRatingId LEFT OUTER JOIN RISKRATINGTYPE rtypf ON rtypf.RISKRATINGTYPEID = x.FutureRatingId LEFT OUTER JOIN ( SELECT cp.CORPORATERISKASSESSMENTID, r.RISKCATEGORY FROM CORPORATERISKASSESSMENT cp LEFT OUTER JOIN CORPORATERISKASSESSMENTCATEGORY c ON c.CORPORATERISKASSESSMENTID = cp.CORPORATERISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 1 ) rcat ON o.CORPORATERISKASSESSMENTID = rcat.CORPORATERISKASSESSMENTID LEFT OUTER JOIN ( SELECT m.CORPORATERISKASSESSMENTID, STUFF( ( SELECT '; ' + r.RISKCATEGORY FROM CORPORATERISKASSESSMENT v LEFT OUTER JOIN CORPORATERISKASSESSMENTCATEGORY c ON v.CORPORATERISKASSESSMENTID = c.CORPORATERISKASSESSMENTID INNER JOIN RISKCATEGORY r ON r.RISKCATEGORYID = c.RISKCATEGORYID WHERE c.IsPrimary = 0 AND v.CORPORATERISKASSESSMENTID = m.CORPORATERISKASSESSMENTID AND r.ParentCategoryID IS NULL FOR XML PATH('') ), 1, 1, '' ) AS [SecRiskCategories] FROM CORPORATERISKASSESSMENT m GROUP BY m.CORPORATERISKASSESSMENTID ) sriskcat ON sriskcat.CORPORATERISKASSESSMENTID = o.CORPORATERISKASSESSMENTID