Mine

create view SPTasselect country,state =casewhen rtrim(isnull(state,'')) <> '' then statewhen rtrim(isnull(province, '')) <> '' then provincewhen rtrim(isnull(territory, '')) <> '' then territoryelse nullendfrom propertiesgocreate view Statesasselect Country, State from SPTwhere not state is nullgroup by Country, Statego