Начальный запрос имеет следующий вид :
Select 5 v1 , 10 v2 , 11 v3 From dual
Union
Select 50 v1 , 100 v2 , 110 v3 From dual
Резултат :
|
|
|
V1
|
V2
|
V3
|
|
1
|
|
5
|
10
|
11
|
|
2
|
|
50
|
100
|
110
|
С помощью оператора UNPIVOT можно трансформировать высше указанный запрос :
Select Rownum NewRN,rn,cols, vals
From (
Select Rownum rn,table1.*
From (
Select 5 v1 , 10 v2 , 11 v3 From dual
Union
Select 50 v1 , 100 v2 , 110 v3 From dual
) table1
) table1
UnPivot include nulls (Vals for cols in(v1 , v2 ,v3));
-----------------------------------------------------------------
2. с оператором with :
With
t as (
Select Rownum rn,table1.*
From (
Select 5 v1 , 10 v2 , 11 v3 From dual
Union
Select 50 v1 , 100 v2 , 110 v3 From dual
) table1
)
Select Rownum NewRN,rn,cols, vals
From t UnPivot include nulls (Vals for cols in(v1 , v2 ,v3));
Резултат :
|
NEWRN
|
|
RN
|
COLS
|
VALS
|
|
1
|
|
1
|
V1
|
5
|
|
2
|
|
1
|
V2
|
10
|
|
3
|
|
1
|
V3
|
11
|
|
4
|
|
2
|
V1
|
50
|
|
5
|
|
2
|
V2
|
100
|
|
6
|
|
2
|
V3
|
110 |
