declare @offices table (
   Office        varchar(10),
   OfficeType    varchar(10),
   Employee      varchar(50),
   EmployeeType  varchar(20)
   )

insert into @offices
values
   ('East', 'Division', null, null),
   ('East', 'Division', 'Bill Smith', 'Full Time'),
   ('East', 'Division', 'Sally Smith', 'Part Time'),
   ('East', 'Division', 'Bill Smith', 'Maternity Leave'),
   ('West', 'Field', null, null),
   ('West', 'Field', 'John Smith', 'Part Time'),
   ('South', 'Division', null, null),
   ('South', 'Division', 'Sue Smith', 'Part Time'),
   ('South', 'Division', 'John Smith', 'Part Time'),
   ('South', 'Division', 'Jimmy Smith', 'Part Time'),
   ('North', 'Division', null, null)

;with Divisions
as (
select
   Office,
   OfficeType,
   EmployeeType,
   count(EmployeeType) over (partition by Office) CountTypes
from
   @offices
where
   OfficeType = 'Division'
),
DivSingleType 
as (
select
   Office,
   count (distinct EmployeeType) CountTypes
from
   divisions
group by
   Office
having
   count (distinct EmployeeType) = 1
)
select
   Office,
   OfficeType,
   EmployeeType
from Divisions
where CountTypes = 0

union all

select distinct
   a.Office,
   a.OfficeType,
   a.EmployeeType
from
   Divisions a
inner join
   DivSingleType b
      on   a.Office = b.Office
      and   a.EmployeeType is not null

Select Office,OfficeType,null
from
(
Select Office,OfficeType,Count(*) CNT
from @offices
Where OfficeType = 'Division'
Group by Office,OfficeType
Having Count(*) = 1
)P

Union

Select A.Office,A.OfficeType,A.EmployeeType
from
(
Select Office,OfficeType,EmployeeType,Count(*) CNTA
from @offices
Where OfficeType = 'Division'
Group by Office,OfficeType,EmployeeType
Having Count(*) > 1
)A
inner join
(
Select Office,OfficeType,Count(*) CNTB
from @offices
Where OfficeType = 'Division'
Group by Office,OfficeType
Having Count(*) > 1
)B on A.Office = B.Office and A.OfficeType = B.OfficeType and A.CNTA = B.CNTB