How to consolidate two tabs with different headers into one pivot table?

Question

I have two tabs to consolidate, they look like this:

Tab1: Employee Course Reimbursement

Emp_ID    Emp_Name    Institute_Name    Course_Name    Cost
000001    John        A                 PM             $100
000001    John        B                 DS             $150
000002    Nancy       B                 PM             $130

Tab2: Employee Membership Reimbursement

Emp_ID    Emp_Name    Membership?    Amount_Requested    Amout_Approved
000001    John        T                 $150             $100
000001    John        T                 $100             $100
000003    Steven      T                 $200             $100

So I would like to combine the tabs like this:

Emp_ID    Emp_Name    Institute_Name    Course_Name    Cost     Membership?    Amount_Requested    Amout_Approved
000001    John        A                 PM             $100
000001    John        B                 DS             $150
000002    Nancy       B                 PM             $130
000001    John                                                      T                 $150             $100
000001    John                                                      T                 $100             $100
000003    Steven                                                    T                 $200             $100

And the pivot table would be like

Emp_ID    Emp_Name    Cost
000001    John        $450
000002    Nancy       $130
000003    Steven      $100

I don't know how to combine the tabs with different headers. Can anyone help?

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
select Emp_ID, Emp_Name, sum(Cost) as Cost
from (
  select Emp_ID, Emp_Name, Cost
  from `project.dataset.table1`
  union all
  select Emp_ID, Emp_Name, Amout_Approved
  from `project.dataset.table2`
)
group by Emp_ID, Emp_Name     

if to apply to sample data from your question - output is

To get the combined table - use below

#standardSQL
select Emp_ID, Emp_Name, Institute_Name, Course_Name, Cost, null Membership, null Amount_Requested, null Amout_Approved
from `project.dataset.table1`
union all
select Emp_ID, Emp_Name, null, null, null, Membership, Amount_Requested, Amout_Approved
from `project.dataset.table2`

with output