how to convert a table into another based on each Columns in SQL

Question

I have the following table(each name is unique):

TABLE1:

+----+----------+----------------+----------------+----------------+----------------+
| id | workflow | tire1_approver | tire2_approver | tire3_approver | tire4_approver |
+----+----------+----------------+----------------+----------------+----------------+
| 1  | 1        | John           | Mike           | Tom            | Kevin          |
+----+----------+----------------+----------------+----------------+----------------+
| 2  | 2        | Mike           | Andrew         | An             | Bob            | 
+----+----------+----------------+----------------+----------------+----------------+

I need to translate it into the following table, a person can appear more than once:

TABLE2:

    +----+--------+----------------+-----------+----------------------+
    | ID | Name   | Position       |  Workflow | upper_level_approver |
    +----+--------+----------------+-----------+----------------------+
    | 1  | John   | tire1_approver | 1         | Mike                 |
    +----+--------+----------------+-----------+----------------------+
    | 2  | Mike   | tire2_approver | 1         | Tom                  |
    +----+--------+----------------+-----------+----------------------+
    | 3  | Tom    | tire3_approver | 1         | Kevin                |
    +----+--------+----------------+-----------+----------------------+
    | 4  | Kevin  | tire4_approver | 1         | N/A                  |
    +----+--------+----------------+-----------+----------------------+
    | 5  | Mike   | tire1_approver | 2         | Andrew               |
    +----+--------+----------------+-----------+----------------------+
    | 6  | Andrew | tire2_approver | 2         | An                   |
    +----+--------+----------------+-----------+----------------------+
    | 7  | An     | tire3_approver | 2         | Bob                  |
    +----+--------+----------------+-----------+----------------------+
    | 8  | Bob    | tire4_approver | 2         | N/A                  |
    +----+--------+----------------+-----------+----------------------+

I'm using sql developer, i have tried loop and join but aren't able to get to what I want.

TABLE1 and TABLE2 are both in the database, Ultimately I would like to store this in the a stored Procedure, when the Front end makes an update to TABLE1, it also calls this procedure and automatically updates TABLE2.

please help

Answer 1

Depending on your Oracle version, you may or may not be able to use the solution below. It uses the UNPIVOT operator, so it requires Oracle 11.2 or higher. In earlier versions, you can use a cross join.

   with
     table1 ( id, workflow, tire1_approver, tire2_approver,
                                            tire3_approver, tire4_approver )
       as (
            select 1, 1, 'John', 'Mike'  , 'Tom', 'Kevin' from dual union all
            select 2, 2, 'Mike', 'Andrew', 'An' , 'Bob'   from dual
          )
-- End of simulated table (for testing; not part of the solution).
-- SQL query begins BELOW THIS LINE
select  id, name, position, workflow,
        lead(name, 1, 'N/A') over ( partition by id, workflow order by lvl )
          as upper_level_approver
from    table1
unpivot ( name for (position, lvl) in ( tire1_approver as ('tire1_approver', 1),
                                        tire2_approver as ('tire2_approver', 2),
                                        tire3_approver as ('tire3_approver', 3),
                                        tire4_approver as ('tire4_approver', 4)
                                      )
        )
;

ID  NAME    POSITION         WORKFLOW  UPPER_LEVEL_APPROVER
--  ------  --------------  ---------  --------------------
 1  John    tire1_approver          1  Mike  
 1  Mike    tire2_approver          1  Tom   
 1  Tom     tire3_approver          1  Kevin 
 1  Kevin   tire4_approver          1  N/A     
 2  Mike    tire1_approver          2  Andrew
 2  Andrew  tire2_approver          2  An    
 2  An      tire3_approver          2  Bob   
 2  Bob     tire4_approver          2  N/A

Answer 2

Here is a solution using a cross join (instead of the unpivot operator). This will work in older versions of Oracle. Simulated data and query output are the same as in my other answer (with unpivot).

The OP did not mention whether null is possible in the input table (in the approver columns). If it is possible, this cross join solution will handle them differently from the unpivot solution. The unpivot solution can be modified to produce the same result as the cross join, by using the optional include nulls directive in the unpivot clause. Or, if the null values should not be included, that can be handled in the cross join solution with a where condition.

select id, name, position, workflow,
       lead(name, 1, 'N/A') over (partition by id, workflow order by lvl)
         as upper_level_approver
from   ( select t.id, 
                case h.lvl when 1 then t.tire1_approver
                           when 2 then t.tire2_approver
                           when 3 then t.tire3_approver
                           when 4 then t.tire4_approver
                end
                  as name, 
                case h.lvl when 1 then 'tire1_approver'
                           when 2 then 'tire2_approver'
                           when 3 then 'tire3_approver'
                           when 4 then 'tire4_approver'
                end 
                  as position,
                t.workflow,
                h.lvl
         from   table1 t
                cross join
                ( select level as lvl from dual connect by level <= 4 ) h
    )
;