SQL & PL/SQL

Hi, - I have two related questions. The first one is an attempt to answer the second one, but even if the second question has a different, better answer, I am interested in the first question for its own sake (to see what I can learn from it).

For the first question, see this:

SQL> create table t (col0 number, col1 number, col2 number, col3 number);
Table created.
SQL> insert into t values (1, 3, 55, 2);
SQL> insert into t values (2, 0, 7, 13);
SQL> insert into t values (1, -32, 7, 8);
SQL> insert into t values (3, 15, 4, NULL);
SQL> select * from t;
      COL0       COL1       COL2       COL3
---------- ---------- ---------- ----------
         1          3         55          2
         2          0          7         13
         1        -32          7          8
         3         15          4          
SQL> select col0,
         case col0 when 1 then col1
                   when 2 then col2
                   when 3 then col3
         end   as val
     from t;
      COL0        VAL
---------- ----------
         1          3
         2          7
         1        -32
         3

Table t is given. col0 may only take the values 1, 2 and 3. (I didn't show the constraint, but it's there.) In the query I want to show one row for each row in t; it should show the value in col0, and the value in col$n$ where $n$ is the value from col0. The case statement in the query explains the requirement better than I can.

Question 1: if this was the requirement, is that the best way to do it? More generally, suppose we have a helper table: in the first column we have the values allowed in t.col0, and in the second column there is the name of exactly one column from t (possibly even col0!). Similar to the when... then... pairs in the case statement above. What is the best way to write a query on t, returning a row y for each row x in table t, like this: y(col0) := x(col0) and y(val) := x(col$n$) where col$n$ corresponds to x(col0) in the helper table.

The second question is related to the background for Q1. A legacy table shows a hierarchy, in a horrible way. It looks something like table t; it has one more column, say emp_id, right before col0, and it is the PK (let's say; that is irrelevant.) col0 shows how many levels are above the current emp_id in the hierarchy. col1 always shows the emp_id for the boss of all bosses. (Highest level in the hierarchy; "level 1" is the same "king" for all emp_id's). col2 shows people at the second level in the hierarchy, and for example for all emp_id in the finance department, col2 will be the CFO's id, for all in the IT department col2 will be the CTO's id, etc. - assuming the CFO and the CTO are at the second level, not lower. Now I want to right this mess - what this SHOULD be is a table with just two columns, showing emp_id and the immediate boss. Assuming the current table structure is as I described it, what I tried in Q1 is what I was able to come up with to put it right. (The complete solution would require that I pull the emp_id into the query as well, ...) Is there a better approach?

And since I promised to ask only two questions, here is another one... is there any good reading you would recommend for such questions? Thank you, - mathguy-ro