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 |
--------------------------------------------------------------------------------------
