Pivot

SELECT 'Total Cost' as 'Total Accumulated Cost', [General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total]
FROM
( SELECT       CASE WHEN ISNUMERIC(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT)= 1 THEN convert(float,isnull(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT,0)) ELSE 0 END AS B1_CHECKLIST_COMMENT, dbo.BCHCKBOX.B1_CHECKBOX_DESC
FROM         dbo.B1PERMIT INNER JOIN
                      dbo.BCHCKBOX ON dbo.B1PERMIT.SERV_PROV_CODE = dbo.BCHCKBOX.SERV_PROV_CODE
                      AND dbo.B1PERMIT.B1_PER_ID1 = dbo.BCHCKBOX.B1_PER_ID1 AND
                      dbo.B1PERMIT.B1_PER_ID2 = dbo.BCHCKBOX.B1_PER_ID2 AND dbo.B1PERMIT.B1_PER_ID3 = dbo.BCHCKBOX.B1_PER_ID3
WHERE     dbo.BCHCKBOX.B1_CHECKBOX_DESC IN ('General', 'Plumbing', 'Gas Piping', 'Electric', 'Sprinkler', 'Mechanical', 'Total')
) AS SourceTable
PIVOT
( AVG(B1_CHECKLIST_COMMENT)
FOR B1_CHECKBOX_DESC IN ([General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total])
) AS PivotTable

AND OUTPUT

Total Accumulated Cost General Plumbing Gas Piping Electric Sprinkler Mechanical Total

Total Cost 11223.417092511 1516.51906158358 2334.05660377358 3149.06866927593 0 1109.60683012259 11558.0263052544

AND THIS OUTPUTS IT BY ID 

SELECT * --'Total Cost' as 'Total Accumulated Cost', [General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total]
FROM
( SELECT      b1_alt_id, CASE WHEN ISNUMERIC(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT)= 1 THEN convert(float,isnull(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT,0)) ELSE 0 END AS B1_CHECKLIST_COMMENT, dbo.BCHCKBOX.B1_CHECKBOX_DESC
FROM         dbo.B1PERMIT INNER JOIN
                      dbo.BCHCKBOX ON dbo.B1PERMIT.SERV_PROV_CODE = dbo.BCHCKBOX.SERV_PROV_CODE 
                      AND dbo.B1PERMIT.B1_PER_ID1 = dbo.BCHCKBOX.B1_PER_ID1 AND 
                      dbo.B1PERMIT.B1_PER_ID2 = dbo.BCHCKBOX.B1_PER_ID2 AND dbo.B1PERMIT.B1_PER_ID3 = dbo.BCHCKBOX.B1_PER_ID3
WHERE     dbo.BCHCKBOX.B1_CHECKBOX_DESC IN ('General', 'Plumbing', 'Gas Piping', 'Electric', 'Sprinkler', 'Mechanical', 'Total')
) AS SourceTable
PIVOT
( AVG(B1_CHECKLIST_COMMENT)
FOR B1_CHECKBOX_DESC IN ([General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total])

) AS PivotTable

Basic Query using Outer Apply

SELECT DISTINCT B1_ALT_ID
, B1_PARCEL_NBR
, ATTRIB_VALUE_1
, ATTRIB_VALUE_2
, ATTRIB_VALUE_3
, CASE
when LEFT(isnull(ATTRIB_VALUE_2,''),1) = ''
then 'No Designation'
when LEFT(isnull(ATTRIB_VALUE_2,''),1) = '1'
then 'Residential'
else 'Commercial'
END AS 'parcel category'
,(CASE
WHEN B1P."B1_PER_TYPE" = '12FAM'
THEN 'RESIDENTIAL'
WHEN B1P."B1_PER_TYPE" = 'Other'
THEN 'COMMERCIAL'
ELSE 'NO DESIGNATION'
END) AS 'Comm or Res by group'
, (convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Total Estimated Costs'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Job Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Work Value'),0))) AS 'Total Estimated Cost'
 
 FROM dbo.B1PERMIT B1P

 OUTER APPLY (SELECT top 1 isnull(B1_PARCEL_NBR,'') AS 'B1_PARCEL_NBR'
FROM "dbo"."B3PARCEL" AS "B3PARCEL"
WHERE "B3PARCEL"."SERV_PROV_CODE"=B1P."SERV_PROV_CODE"
AND "B3PARCEL"."B1_PER_ID1"=B1P."B1_PER_ID1"
AND "B3PARCEL"."B1_PER_ID2"=B1P."B1_PER_ID2"
AND "B3PARCEL"."B1_PER_ID3"=B1P."B1_PER_ID3" ) AS A

 OUTER APPLY (SELECT top 1 isnull(ATTRIB_VALUE_1,'') AS 'ATTRIB_VALUE_1'
, isnull(ATTRIB_VALUE_2,'') AS 'ATTRIB_VALUE_2'
, isnull(ATTRIB_VALUE_3,'') AS 'ATTRIB_VALUE_3'
FROM "dbo"."Parcel_address" AS "Parcel_address"
WHERE "Parcel_address"."L1_PARCEL_NBR"=B1_PARCEL_NBR) AS B

 WHERE  (b1_file_dd >= @BeginDate)
AND (b1_file_dd < DATEADD(day,1,@EndDate))
and isnull(B1_APPL_CLASS, 'na') <> 'INCOMPLETE TMP'
and isnull(B1_APPL_CLASS, 'na') <> 'INCOMPLETE EST'

AND B1P."B1_PER_GROUP" = 'building'
AND b1_per_sub_type != 'health'
AND B1_ALT_ID not like '%vncy%'

AND B1P."REC_STATUS"='A'
--AND B1_PER_SUB_TYPE != 'Temporary Permit'
AND B1P.b1_alt_id not like '%tmp%'
--AND "B1PERMIT".B1_PER_TYPE <> 'C of I'
--AND "B1PERMIT".B1_PER_TYPE <> 'ZBA'
                AND B1P."B1_PER_TYPE"<>'complaint'
AND B1P."B1_APPL_STATUS" != 'Cancelled'
AND (convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Total Estimated Costs'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Job Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Work Value'),0))
) >= @ATWHATVALUE