Amour22015
asked on
SQL Server 2012 - Rows need to be one row
Hi Experts,
I have this query:
I am looking for this to be:
NeedToBeOne Fail Pass All Percenfailed PercentCompliance Success ComplianceAssetKey OwnershipGroupKey
Please fix to only one 38 47 85 44.71 55.29 No 1076 499
ScanPolicyGroupKey
19
Please Help and thanks...
I have this query:
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)'
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,
I am looking for this to be:
NeedToBeOne Fail Pass All Percenfailed PercentCompliance Success ComplianceAssetKey OwnershipGroupKey
Please fix to only one 38 47 85 44.71 55.29 No 1076 499
ScanPolicyGroupKey
19
Please Help and thanks...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thanks...
Open in new window