Rows to Columns

Distributing Left to Right - Other Methods

In this topic we compare the two methods for distributing row values across columns left to right discussed earlier, PIVOT and MAX DECODE, with two other methods sometimes used, LEAD and MODEL. At the end of this topic a Performance Comparison of all techniques is given.

Test Data

The following test data will be used in each solution presented.

```desc t6
Name                                           Null?    Type
---------------------------------------------- -------- -------------------------------
GROUP_KEY                                               VARCHAR2(10)
VAL                                                     VARCHAR2(10)

break on group_key duplicates skip 1

select *
from   t6
where  group_key in ( 'G0','G1','G2')
order  by group_key, val ;
```
```GROUP_KEY  VAL
---------- ----------
G0         a0
G0         a1
G0         a2
G0         a3
G0         a4

G1         a0
G1         a1
G1         a2
G1         a3
G1         a4

G2         a0
G2         a1
G2         a2
G2         a3
G2         a4
```
```column number format 999,999,999

select
count(*) as total_rows ,
count( distinct group_key ) as distinct_group_keys ,
5 as rows_per_group_key
from t6 ;
```
```TOTAL_ROWS DISTINCT_GROUP_KEYS ROWS_PER_GROUP_KEY
---------- ------------------- ------------------
100000               20000                  5
```

Each test will return a result set that looks like this.

```
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0         a0         a1         a2
G0         a3         a4

G1         a0         a1         a2
G1         a3         a4

G2         a0         a1         a2
G2         a3         a4

...
```

PIVOT

First we test the PIVOT solution presented in Distributing Left to Right .

```PIVOT:

select
group_key ,
col_0 ,
col_1 ,
col_2
from
( select
group_key,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key ,
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
val
from t6
) t6a pivot
( max(val)
for pivot_key in
( 0 as col_0 ,
1 as col_1 ,
2 as col_2
)
)
order by group_key, row_num ;

Test Complete
```
```
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0         a0         a1         a2
G0         a3         a4

G1         a0         a1         a2
G1         a3         a4

G2         a0         a1         a2
G2         a3         a4

...
```

MAX DECODE

Next we test the MAX DECODE solution presented in Distributing Left to Right .

```MAX DECODE:

select
group_key ,
max( decode( pivot_key, 0, val, null ) ) col_0 ,
max( decode( pivot_key, 1, val, null ) ) col_1 ,
max( decode( pivot_key, 2, val, null ) ) col_2
from
( select
group_key,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key ,
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
val
from t6
) t6a
group by group_key, row_num
order by group_key, row_num ;

Test Complete
```
```
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0         a0         a1         a2
G0         a3         a4

G1         a0         a1         a2
G1         a3         a4

G2         a0         a1         a2
G2         a3         a4

...
```

This method uses the LEAD function.

```LEAD:

select group_key, col_0, col_1, col_2
from
( select
group_key ,
pivot_key ,
val as col_0 ,
over( partition by group_key, row_num order by val )
as col_1,
over( partition by group_key, row_num order by val )
as col_2
from
( select
group_key,
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key ,
val
from t6
) t6a
) t6b
where pivot_key = 0
order by group_key ;

Test Complete
```
```
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0         a0         a1         a2
G0         a3         a4

G1         a0         a1         a2
G1         a3         a4

G2         a0         a1         a2
G2         a3         a4

...
```

MODEL Solution

This method uses the MODEL clause of the SELECT command (see SQL Features Tutorials: MODEL Clause if you are unfamiliar with MODEL).

```MODEL:

select group_key, col_0, col_1, col_2
from   t6
model
return updated rows
partition by ( group_key )
dimension by
(
trunc
( ( row_number() over ( partition by group_key order by val ) - 1 ) / 3 )
as row_num ,
mod( row_number() over ( partition by group_key order by val ) - 1, 3 )
as pivot_key
)
measures( val as col_0, val as col_1, val as col_2 )
rules upsert
(
col_1[any,0] = col_0[cv(),1] ,
col_2[any,0] = col_0[cv(),2]
)
order by group_key, row_num ;

Test Complete
```
```
GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0         a0         a1         a2
G0         a3         a4

G1         a0         a1         a2
G1         a3         a4

G2         a0         a1         a2
G2         a3         a4

...
```

Performance Comparison

The following table shows database statistics where values for one method differ by more than 100 from another method.

```
Metric                     PIVOT     MAX DECODE LEAD      MODEL
-------------------------- --------- ---------- --------- ---------
Elapsed Time (1/100 sec)   369       348        350       353
Latch Gets Total           6,746     6,602      7,158     6,866
session pga memory max     7,471,104 7,405,568  7,405,568 7,405,568
table scan rows gotten     123,928   100,000    100,000   100,000
session pga memory         -196,608  -196,608   -262,144  -262,144

See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
```

Note: The tests were performed using SQL*Plus against an Oracle 11g Enterprise Edition Release 11.1.0.6.0 database running on Oracle Enterprise Linux 5. The tests were performed on a single CPU machine. On multi-CPU machines the MODEL solution may perform better than reported here.