Find all sectors represented per each Unique Agency on a table

Does this format work also?

select distinct H.agency, 
SUM(CASE when R.Sector = 'Business' then 1 else 0 end) as [Business] ,SUM(CASE when R.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer],SUM(CASE when R.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident],SUM(CASE when R.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based] ,SUM(CASE when R.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare] ,SUM(CASE when R.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies] ,SUM(CASE when R.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement],SUM(CASE when R.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when R.Sector = 'Media' then 1 else 0 end) as [Media] ,SUM(CASE when R.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian],SUM(CASE when R.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic],SUM(CASE when R.Sector = 'Schools' then 1 else 0 end) as [Schools],SUM(CASE when R.Sector = 'Youth' then 1 else 0 end) as [Youth]
from tblorghours H inner join tblOrgRegistrations R on H.Regid = R.RegID 
where H.activitydate > '7/1/17' and activitydate < '6/30/18' group by h.agency order by h.agency

Select allOpen in new window

Actually yours is better

select  H.agency 
	, Business = CASE WHEN SUM(Business) > 1 THEN 1 ELSE 0 END
	, [Civic-Volunteer] = CASE WHEN SUM([Civic-Volunteer]) > 1 THEN 1 ELSE 0 END
	, [Community Resident] = CASE WHEN SUM([Community Resident]) > 1 THEN 1 ELSE 0 END
	, [Faith Based] = CASE WHEN SUM([Faith Based]) > 1 THEN 1 ELSE 0 END
	, Healthcare = CASE WHEN SUM(Healthcare) > 1 THEN 1 ELSE 0 END
	, [Human Support Agencies] = CASE WHEN SUM([Human Support Agencies]) > 1 THEN 1 ELSE 0 END
	, [Law Enforcement] = CASE WHEN SUM([Law Enforcement]) > 1 THEN 1 ELSE 0 END
	, [Local Government] = CASE WHEN SUM([Local Government]) > 1 THEN 1 ELSE 0 END
	, Media = CASE WHEN SUM(Media) > 1 THEN 1 ELSE 0 END
	, [Parent or Guardian] = CASE WHEN SUM([Parent or Guardian]) > 1 THEN 1 ELSE 0 END
	, Philanthropic = CASE WHEN SUM(Philanthropic) > 1 THEN 1 ELSE 0 END
	, Schools = CASE WHEN SUM(Schools) > 1 THEN 1 ELSE 0 END
	, Youth = CASE WHEN SUM(Youth) > 1 THEN 1 ELSE 0 END

	from tblorghours H inner join tblOrgRegistrations R on H.Regid = R.RegID 
where H.activitydate > '7/1/17' and activitydate < '6/30/18' group by h.agency order by h.agency

Select allOpen in new window

Errors I get are:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Business'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Civic-Volunteer'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Community Resident'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Faith Based'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'Healthcare'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Human Support Agencies'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'Law Enforcement'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Local Government'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Media'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Parent or Guardian'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Philanthropic'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Schools'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'Youth'.

Those are the actual column names though

this was correct for what I presented however I have stated the wrong question.  I am re posting the correct question