select likelihoodPercent, 'p' + convert( varchar, likelihoodpercent * 100 ) + '_' + cast( stageId as varchar ) pipelineStageCode from tbl_SPL_Stages with (nolock) where ownerId = and ownerType = and statusCode = 'A' order by likelihoodPercent with pipelineAlerts as ( select 'Stage' pipelineAlert, 'p' + convert( varchar, likelihoodPercent * 100 ) + '_' + cast( stageId as varchar ) pipelineStage , #_quantityColumn#, opportunityId from tbl_DW_Opportunities#_tableSuffix# with (nolock) where pastDue = 'Y' and partnerTerritoryId in ( ) and effectiveRepId in ( ) and ( 1 = 2 or territoryPath like ) and effectiveRepId in () and mfr_Id = and budgetCategoryId in () and effectiveRepOwnerType = and ( 1 = 2 or productHierarchyPath like ) and ( 1 = 2 or productSetTags like ) ), pipelineSummary as ( select pipelineAlert, pipelineStage, sum( #_quantityColumn# ) as quantity, count( distinct opportunityId ) opportunities from pipelineAlerts group by pipelineAlert, pipelineStage ), pipelinePivot as ( select pipelineAlert ,coalesce( #_pipelineStageCode#, 0 ) #_pipelineStageCode# from pipelineSummary pivot( sum( quantity ) for pipelineStage in ( #_pipelineStageCodes# )) p ) select pipelineAlert ,sum( #_pipelineStageCode# ) #_pipelineStageCode# from pipelinePivot group by pipelineAlert for JSON PATH, INCLUDE_NULL_VALUES