## Rows to String

### MODEL Method 3 - Explained

This tutorial explains how the Rows to String solution presented in MODEL Method 3 works. Note: If you are unfamiliar with Oracle's MODEL feature you should review the tutorials at SQL Features Tutorials: MODEL Clause tutorial before proceeding.

The basic solution in the preceding tutorial looked like this.

```set null "(null)"

select group_key, substr( string, 2 ) as string
from   t
where  1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from  t
)
dimension by( val_number )
measures    ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures    ( cast(null as varchar2(65)) as string  ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until  ( presentv(r.val[iteration_number+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
```
```GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,a,b,b
Group 5    a,b,d,e,
Group 6    (null)
Group 7    ,
Group 8    a,a,b,b,,
(null)     (null)
```

Notes:

1. 65 = v * ng + (ng-1)

• v = maximum length of VAL values, i.e. 10
• ng = maximum number of VALs with the same GROUP_KEY, i.e. 6
• nt = total number of rows in T
2. use any number >= nt + 1

To understand how this query works we will look at intermediate steps in its processing. First, let's see what the query does without the REFERENCE or RULES clauses.

```select group_key, string
from   t
where  1=2
model
main t_main
dimension by( cast(null as integer) as i, group_key )
measures    ( cast(null as varchar2(65)) as string  )
()
order by 1 ;

no rows selected

```

This gives us an empty result set. We do not need any rows at this point because the RULES clause to follow will create all the required rows for us. The `1=2` condition is included to prevent the DUAL row from creating an extraneous row of nulls in the end result.

Now let's examine the REFERENCE model query.

```select
row_number() over (order by val) as val_number ,
group_key ,
val
from  t
order by val ;
```
```VAL_NUMBER GROUP_KEY  VAL
---------- ---------- ----------
1 Group 1    a
2 Group 8    a
3 Group 5    a
4 Group 3    a
5 Group 2    a
6 Group 8    a
7 Group 4    a
8 Group 4    a
9 Group 5    b
10 Group 4    b
11 Group 4    b
12 Group 8    b
13 Group 8    b
14 Group 3    b
15 Group 2    b
16 Group 3    c
17 Group 5    d
18 Group 5    e
19 Group 8    (null)
20 Group 8    (null)
21 Group 7    (null)
22 Group 7    (null)
23 Group 6    (null)
24 (null)     (null)
25 Group 5    (null)
```

As you can see, it returns two base table columns plus a control column called VAL_NUMBER, which provides a dimension key for each row.

Now let's add the RULES clause. We will change the ITERATION clause slightly so we can demonstrate what happens in each iteration of the RULES loop.

```variable v_total_iterations number

execute :v_total_iterations := 1

PL/SQL procedure successfully completed.

select group_key, string
from   t
where  1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from  t
)
dimension by( val_number )
measures    ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures    ( cast(null as varchar2(65)) as string  ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until  ( iteration_number + 1 = :v_total_iterations )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;

no rows selected

```

This first iteration is uneventful. We deliberately do nothing in this iteration to prevent an extraneous row from appearing in the end result when the reference model is empty. This is required because ITERATE always performs at least one iteration, regardless of the UNTIL condition. Since there is no way to stop ITERATE from looping when we have an empty reference model we instead allow it to loop at least once and do nothing in the first iteration.

The `for i from 1 to sign(iteration_number) increment 1` logic ensures that no cell is created during the first iteration, i.e. when ITERATION_NUMBER is 0. When ITERATION_NUMBER is 1 or greater this logic always sets dimension "i" to "1".

Now let's see what happens after nine iterations.

```execute :v_total_iterations := 9

/
```
```GROUP_KEY  STRING
---------- ---------------
Group 1    ,a
Group 2    ,a
Group 3    ,a
Group 4    ,a,a
Group 5    ,a
Group 8    ,a,a
```

In the first nine iterations one row is created for each distinct GROUP_KEY value and all the "a" VAL values are appended to the appropriate STRING column. The leading "," will be stripped off later.

Iterations 10 to 16 append all the "b" values to the appropriate STRINGs.

```execute :v_total_iterations := 16

/
```
```GROUP_KEY  STRING
---------- ---------------
Group 1    ,a
Group 2    ,a,b
Group 3    ,a,b
Group 4    ,a,a,b,b
Group 5    ,a,b
Group 8    ,a,a,b,b
```

Now let's use the original ITERATE clause.

```select group_key, string
from   t
where  1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from  t
)
dimension by( val_number )
measures    ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures    ( cast(null as varchar2(65)) as string  ) -- note 1
rules
upsert
ITERATE( 1000000 ) -- NOTE 2
UNTIL  ( PRESENTV(R.VAL[ITERATION_NUMBER+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
```
```GROUP_KEY  STRING
---------- ---------------
Group 1    ,a
Group 2    ,a,b
Group 3    ,a,b,c
Group 4    ,a,a,b,b
Group 5    ,a,b,d,e,
Group 6    ,
Group 7    ,,
Group 8    ,a,a,b,b,,
(null)     ,
```

We use 1,000,000 as the argument to ITERATE() because we do not know, in advance, how many iterations will be required. Any number can be used here, as long as it is greater than the total number of rows returned by the REFERENCE model query. The UNTIL clause controls the actual number of iterations.

In the UNTIL clause, the `PRESENTV(R.VAL[ITERATION_NUMBER+1],1,0)=0` expression will be FALSE for iterations 0 to 24. When ITERATION_NUMBER reaches 25 there are no more values in our reference model to process so we can stop looping. `PRESENTV(R.VAL[ITERATION_NUMBER+1],1,0)=0` will be TRUE at this point causing iterations to stop.

Finally, we apply the `substr( string, 2 )` expression to the SELECT clause to strip the leading "," characters from STRING and produce the end result.

```select group_key, substr( string, 2 ) as string
from   t
where  1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from  t
)
dimension by( val_number )
measures    ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures    ( cast(null as varchar2(65)) as string  ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until  ( presentv(r.val[iteration_number+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
```
```GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,a,b,b
Group 5    a,b,d,e,
Group 6    (null)
Group 7    ,
Group 8    a,a,b,b,,
(null)     (null)
```

Last, but not least, whenever working with UPSERT and UPSERT ALL rules we should always verify that our rules do not introduce unwanted rows when the result set should really be empty. Below we see that MODEL Method 3 passes the test.

```set feedback on

select group_key, substr( string, 2 ) as string
from   t
where  1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from  t
WHERE 1=2 -- forces an empty reference model
)
dimension by( val_number )
measures    ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures    ( cast(null as varchar2(65)) as string  ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until  ( presentv(r.val[iteration_number+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;

no rows selected

```

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-12085.html]SQL Snippets: Rows to String - MODEL Method 3 - Explained[/url]