CREATE function fn_ConcatSiteInspectionObservations
(
	@site_inspection_schedule_id int,
	@site_id int
)
returns varchar(8000)
as 
begin
	
declare @txtObservations varchar(8000);

set @txtObservations = '';

select 
	@txtObservations = @txtObservations + --coalesce(convert(varchar(8000), todos), '')
	case coalesce(convert(varchar(8000), observations), '')
	when 'None.' then ''
	else coalesce(convert(varchar(8000), observations), '')
	end

from 
	site_inspection
where
	site_inspection_schedule_id = @site_inspection_schedule_id
	and site_id = @site_id

return @txtObservations;

end

select 
	site_inspection_schedule_id, site_id,
	dbo.fn_ConcatSiteInspectionObservations(site_inspection_schedule_id, site_id) as observations
from
	site_inspection
where
	...