SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1
    ([OfficeID] varchar(8), [Emp Name] varchar(5), [Department] varchar(9), [Status] varchar(10))
;
    
INSERT INTO table1
    ([OfficeID], [Emp Name], [Department], [Status])
VALUES
    ('Office_1', 'Emp 1', 'Marketing', 'Active'),
    ('Office_1', 'Emp 2', 'Marketing', 'Active'),
    ('Office_1', 'Emp 3', 'Marketing', 'Non-Active'),
    ('Office_2', 'Emp 4', 'IT', 'Active'),
    ('Office_2', 'Emp 5', 'Finance', 'Active'),
    ('Office_2', 'Emp 6', 'Finance', 'Non-Active'),
    ('Office_2', 'Emp 7', 'Finance', 'Active');

CREATE TABLE table2
    ([officeID] varchar(8), [OfficeName] varchar(8))
;
    
INSERT INTO table2
    ([officeID], [OfficeName])
VALUES
    ('Office_1', 'Office A'),
    ('Office_2', 'Office B');
9 rows affected

select 
  t2.officename,
  sum(case when department = 'Marketing' and status = 'Active' then 1 else 0 end) MarketingExecutive, 
  sum(case when department = 'Finance' and status = 'Active' then 1 else 0 end) FinanceExecutive,
  sum(case when department = 'IT' and status = 'Active' then 1 else 0 end) ITExecutive
from table1 t1 inner join table2 t2
on t2.officeid = t1.officeid
group by t2.officename


officename
MarketingExecutive
FinanceExecutive
ITExecutive


Office A
2
0
0

Office B
0
2
1


select 
  t2.officename,
  count(case when department = 'Marketing' and status = 'Active' then 1 end) MarketingExecutive, 
  count(case when department = 'Finance' and status = 'Active' then 1 end) FinanceExecutive,
  count(case when department = 'IT' and status = 'Active' then 1 end) ITExecutive
from table1 t1 inner join table2 t2
on t2.officeid = t1.officeid
group by t2.officename


officename
MarketingExecutive
FinanceExecutive
ITExecutive


Office A
2
0
0

Office B
0
2
1


Warning: Null value is eliminated by an aggregate or other SET operation.