How to redesign the table? Like recursive CTE

Due to the limited support of Bigquery for recursive CTE, I only have to use some silly ways to deal with the problem. My question is that I have a table below:

Employee0    Employee1    Employee2    Employee3    Employee4
   A            A            B            B            B
   B            B            B            B            B
   C            A            B            B            B

which indicates that B supervises A, and A supervises C.

I would like to have my table like this:

 Employee0    Employee1    Employee2    Employee3    Employee4
   NULL       NULL          NULL           A            B
   NULL       NULL          NULL          NULL          B
   NULL       NULL            C            A            B

I have no idea how to change the table.

Can anyone help? Thanks!

Answer

Below example is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'A' Employee0, 'A' Employee1, 'B' Employee2, 'B' Employee3, 'B' Employee4 UNION ALL
  SELECT 'B', 'B', 'B', 'B', 'B' UNION ALL
  SELECT 'C', 'A', 'B', 'B', 'B' 
)
SELECT 
  emps[SAFE_OFFSET(4)] Employee0,
  emps[SAFE_OFFSET(3)] Employee1,
  emps[SAFE_OFFSET(2)] Employee2,
  emps[SAFE_OFFSET(1)] Employee3,
  emps[SAFE_OFFSET(0)] Employee4
FROM (
  SELECT *, 
    ARRAY(
      SELECT val
      FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':"(.*?)"')) val WITH OFFSET
      GROUP BY val
      ORDER BY MIN(OFFSET) DESC
    ) emps
  FROM `project.dataset.table` t
)   

with result

Row Employee0   Employee1   Employee2   Employee3   Employee4    
1   null        null        null        A           B    
2   null        null        null        null        B    
3   null        null        C           A           B