Solution: Unpivot using cross apply

Cross apply works in one way like a cartesian join. In addtion we can have a ordering of the tables in the from clause.

For that reason it is possible to reference a table inside the from clause.

In our example below we combine the well-known table emp with a virtual table we call CrossApplied. The CROSS APPLY operator makes sure the two table will be combined like a cartesian product. The point is that inside of my virtual table CrossApplied I can access columns which are in emp (aliased t).
By that way I can make rows for columns in emp.

You will find the execution plan below. For those that do wonder: yes, CROSS APPLY is somewhat similar to LATERAL.

The CROSS APPLY solution was indeed in our test quicker than the UNPIVOT clause.

SELECT  empno,
        CrossApplied.Col_name, CrossApplied.Col_value
  FROM emp t
 CROSS APPLY (Select 'Mgr' col_name, t.Mgr col_value from dual
              UNION ALL
              Select 'job', t.job  from dual
              UNION ALL
              Select 'sal', t.sal  from dual) CrossApplied
;


 --------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |       |       |    87 (100)|          |
|   1 |  NESTED LOOPS      |                 |    42 |  1386 |    87   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP             |    14 |   210 |     3   (0)| 00:00:01 |
|   3 |   VIEW             | VW_LAT_C2E3294A |     3 |    54 |     6   (0)| 00:00:01 |
|   4 |    UNION-ALL       |                 |       |       |            |          |
|   5 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------