SQL Server 2012 - Rows need to be one row

Declare
 @REPORTTYPE as varchar(50),
 @POLICY as varchar(2000)

DECLARE @DB_REPORTTYPE as varchar(50)
SET @DB_REPORTTYPE = @REPORTTYPE
DECLARE @DB_POLICY as varchar(2000)
SET @DB_POLICY = @POLICY--'1 Draft VA SQL Server 2012 Policy (2)'

;WITH CTE AS
(
SELECT
	'Please fix to only one' as NeedBothAdded,
	--BOWN.[Branch Region],
	--BOWN.[Branch Network],
	--BOWN.Branch, 
	(Cast(SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as int)) as [Fail],
	(Cast(SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END ) as int)) as [Pass],
	Count(*) as [All], 
	(Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*)) as PercentFailed,
	(Cast( SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END)  as float) /Count(*)) as PercentCompliant,
	Case when ActionableResult = 1 THEN 'No' ELSE 'Yes' END  as Success,
	fac.ComplianceAssetKey,
	fac.OwnershipGroupKey,
	fac.ScanPolicyGroupKey 
	--fac.AssetHostName
	--fac.[CheckName]
FROM [dw].[FactComplianceAssetCheck] as fac 
  inner join dw.brgComplianceScanPolicy as BCP on BCP.ScanPolicyGroupKey = fac.ScanPolicyGroupKey                                   
  inner join [dw].[brgOwnership] as BOWN on BOWN.[OwnershipGroupKey] = fac.OwnershipGroupKey
  inner Join dw.DimComplianceAsset CA On CA.ComplianceAssetKey = Fac.ComplianceAssetKey 
  WHERE fac.IsRowCurrent = 1 
  and Left(BCP.PolicyName, 72) = @DB_POLICY -- Note: Policy name had to be truncated by 72 for the dropdown otherwise way too large, needed to match
  and BOWN.OwnershipType = @DB_REPORTTYPE
  --and
  --IsRowCurrent = 1
and
fac.ScanPolicyGroupKey = 19
and
fac.OwnershipGroupKey = 499
--ActionableResult = 1 
And 
fac.ComplianceAssetKey = 1076
  --and CA.AssetVersion like '%SQL Server 2012%' 
  Group by bown.[Branch Region], Bown.[Branch Network], BOWN.Branch, ActionableResult, fac.ComplianceAssetKey, 
  fac.AssetHostName, fac.OwnershipGroupKey, fac.ScanPolicyGroupKey --, fac.[CheckName]
  --Order By ActionableResult Desc
  --ActionableResult, 
)
SELECT NeedBothAdded,SUM([Fail]) [Fail] ,SUM([Pass]) [Pass] ,SUM([Fail]+[Pass]) [All]
,CAST(( SUM([Fail]+[Pass])  - SUM([Pass]) ) * 100. / SUM([Fail]+[Pass])  AS DECIMAL(20,2)) [Percenfailed]
,CAST(( SUM([Fail]+[Pass])  - SUM([Fail]) ) * 100. / SUM([Fail]+[Pass])  AS DECIMAL(20,2)) [PercentCompliance]
,MIN([Success]) [Success],ComplianceAssetKey , OwnershipGroupKey,  ScanPolicyGroupKey
FROM CTE
GROUP BY NeedBothAdded,ComplianceAssetKey,OwnershipGroupKey,ScanPolicyGroupKey

Select allOpen in new window