create view SPT
as
select country,
state =
case
when rtrim(isnull(state,'')) <> '' then state
when rtrim(isnull(province, '')) <> '' then province
when rtrim(isnull(territory, '')) <> '' then territory
else null
end
from properties

go

create view States
as
select Country, State from SPT
where not state is null
group by Country, State
go