SQL Snippets ™: Tutorials for Oracle Developers

This tutorial explains how the Rows to String solution presented in MODEL Method 2 works. 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)"

clear breaks

select group_key, string
from   t_group_keys
model
  reference r on
    ( select
        group_key ,
        row_number() over ( partition by group_key order by val ) - 1
          as position ,
        val
      from t
    )
    dimension by ( group_key, position )
    measures     ( val )
  main m
    dimension by ( group_key )
    measures     ( cast( null as varchar2(65) ) as string ) -- note 1
    rules
      update
      iterate( 6 ) -- note 2
      (
        string[ any ] =
          string[cv()] ||
          case presentv( r.val[cv(group_key),iteration_number],1,0)
          when 0 then null
          else
            case iteration_number when 0 then null else ',' end ||
            r.val[cv(group_key),iteration_number]
          end
      )
order by group_key ;
 

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)
 

To understand how it works we will look at intermediate steps in processing the query. First consider the reference model query, which selects the individual values we wish to form into a string. It also includes an extra column, POSITION, that numbers each value within each group.

break on group_key skip 1 duplicates

select
  group_key ,
  row_number() over ( partition by group_key order by val ) - 1
    as position ,
  val
from t
;
 

GROUP_KEY    POSITION VAL
---------- ---------- ----------
Group 1             0 a

Group 2             0 a
Group 2             1 b

Group 3             0 a
Group 3             1 b
Group 3             2 c

Group 4             0 a
Group 4             1 a
Group 4             2 b
Group 4             3 b

Group 5             0 a
Group 5             1 b
Group 5             2 d
Group 5             3 e
Group 5             4 (null)

Group 6             0 (null)

Group 7             0 (null)
Group 7             1 (null)

Group 8             0 a
Group 8             1 a
Group 8             2 b
Group 8             3 b
Group 8             4 (null)
Group 8             5 (null)

(null)              0 (null)
 

Next consider columns derived from the T_GROUP_KEYS table, which form the basis for our end result.

clear breaks

select
  group_key ,
  cast( null as varchar2(65) ) as string
from   t_group_keys
order by 1 ;
 

GROUP_KEY  STRING
---------- ---------------
Group 1    (null)
Group 2    (null)
Group 3    (null)
Group 4    (null)
Group 5    (null)
Group 6    (null)
Group 7    (null)
Group 8    (null)
(null)     (null)
 

To calculate the maximum size of the STRING column we multiply the maximum number of possible values in the string (6 in our example) times the maximum size of each value (10 characters) and then add room for the commas that will separate each value (5 commas).

Now let's add the MODEL clause. We will not iterate the rule though so we can demonstrate what happens only in the first iteration. A bind variable takes the place of the ITERATION_NUMBER function.

variable iteration_number number

execute :iteration_number := 0 ;

select group_key, string
from   t_group_keys
model
  reference r on
    ( select
        group_key ,
        row_number() over ( partition by group_key order by val ) - 1
          as position ,
        val
      from t
    )
    dimension by ( group_key, position )
    measures     ( val )
  main m
    dimension by ( group_key )
    measures     ( cast( null as varchar2(65) ) as string ) -- note 1
    RULES
      UPDATE
      (
        STRING[ ANY ] =
          STRING[CV()] ||
          CASE PRESENTV( R.VAL[CV(GROUP_KEY),:ITERATION_NUMBER],1,0)
          WHEN 0 THEN NULL
          ELSE
            CASE :ITERATION_NUMBER WHEN 0 THEN NULL ELSE ',' END ||
            R.VAL[CV(GROUP_KEY),:ITERATION_NUMBER]
          END
      )
order by group_key ;
 

GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a
Group 3    a
Group 4    a
Group 5    a
Group 6    (null)
Group 7    (null)
Group 8    a
(null)     (null)
 

As we can see, the rule changed the STRING values from NULL to the first value in each group.

Now we will iterate the rule once for every possible value position. Each iteration concatenates a new value to the STRING values. This gives us the final result.

select group_key, string
from   t_group_keys
model
  reference r on
    ( select
        group_key ,
        row_number() over ( partition by group_key order by val ) - 1
          as position ,
        val
      from t
    )
    dimension by ( group_key, position )
    measures     ( val )
  main m
    dimension by ( group_key )
    measures     ( cast( null as varchar2(65) ) as string ) -- note 1
    rules
      update
      iterate( 6 ) -- note 2
      (
        string[ any ] =
          string[cv()] ||
          case presentv( r.val[cv(group_key),iteration_number],1,0)
          when 0 then null
          else
            case iteration_number when 0 then null else ',' end ||
            r.val[cv(group_key),iteration_number]
          end
      )
order by group_key ;
 

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)