SQL Snippets ™: Tutorials for Oracle Developers

This tutorial explains how the Rows to String solution presented in MODEL Method 1 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
model
  return updated rows
  partition by ( group_key )
  dimension by ( row_number() over (partition by group_key order by val) as position )
  measures     ( cast( val as varchar2(65) ) as string  )
  rules
    upsert
    iterate( 6 )
      until ( presentv(string[iteration_number+2],1,0) = 0 )
    ( string[0] = string[0] || ',' || string[iteration_number+1] )
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 query without its MODEL clause.

break on group_key skip 1 duplicates

select
  group_key,
  row_number() over (partition by group_key order by val) as position ,
  cast( val as varchar2(65) ) as string
from t
order by group_key, position ;
 

GROUP_KEY    POSITION STRING
---------- ---------- ---------------
Group 1             1 a

Group 2             1 a
Group 2             2 b

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

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

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

Group 6             1 (null)

Group 7             1 (null)
Group 7             2 (null)

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

(null)              1 (null)
 

Now we add the MODEL clause, but without any rules for now. This step simply tells Oracle that we want to partition the results by the GROUP_KEY column, use the POSITION column as a dimension, and use the STRING column as a measure. The output is the same as that of the last query.

select group_key, position, string
from   t
model
  partition by ( group_key )
  dimension by ( row_number() over (partition by group_key order by val) as position )
  measures     ( cast( val as varchar2(65) ) as string  ) -- Note 1
  rules ()
order by group_key, position ;
 

GROUP_KEY    POSITION STRING
---------- ---------- ---------------
Group 1             1 a

Group 2             1 a
Group 2             2 b

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

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

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

Group 6             1 (null)

Group 7             1 (null)
Group 7             2 (null)

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

(null)              1 (null)
 

Note 1: 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 rule, which creates a new row with POSITION = 0 in every partition and concatenates all the partition's values together.

select group_key, position, string
from   t
model
  partition by ( group_key )
  dimension by ( row_number() over (partition by group_key order by val) as position )
  measures     ( cast( val as varchar2(65) ) as string  )
  rules
    upsert
    iterate( 6 )
      until ( presentv(string[iteration_number+2],1,0) = 0 )
    ( string[0] = string[0] || ',' || string[iteration_number+1] )
order by group_key, position ;
 

GROUP_KEY    POSITION STRING
---------- ---------- ---------------
Group 1             0 ,a
Group 1             1 a

Group 2             0 ,a,b
Group 2             1 a
Group 2             2 b

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

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

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

Group 6             0 ,
Group 6             1 (null)

Group 7             0 ,,
Group 7             1 (null)
Group 7             2 (null)

Group 8             0 ,a,a,b,b,,
Group 8             1 a
Group 8             2 a
Group 8             3 b

GROUP_KEY    POSITION STRING
---------- ---------- ---------------
Group 8             4 b
Group 8             5 (null)
Group 8             6 (null)

(null)              0 ,
(null)              1 (null)
 

Since the rows with POSITION = 0 contain all the information we need, we can instruct Oracle to return only these rows using a RETURN UPDATED ROWS directive. At the same time we can trim the leading "," from STRING to yield the final result.

select group_key, position, substr( string, 2 ) as string
from   t
model
  RETURN UPDATED ROWS
  partition by ( group_key )
  dimension by ( row_number() over (partition by group_key order by val) as position )
  measures     ( cast( val as varchar2(65) ) as string  )
  rules
    upsert
    iterate( 6 )
      until ( presentv(string[iteration_number+2],1,0) = 0 )
    ( string[0] = string[0] || ',' || string[iteration_number+1] )
order by group_key, position ;
 

GROUP_KEY    POSITION STRING
---------- ---------- ---------------
Group 1             0 a

Group 2             0 a,b

Group 3             0 a,b,c

Group 4             0 a,a,b,b

Group 5             0 a,b,d,e,

Group 6             0 (null)

Group 7             0 ,

Group 8             0 a,a,b,b,,

(null)              0 (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 is initially empty. Below we see that MODEL Method 1 passes the test.

set feedback on

select group_key, position, substr( string, 2 ) as string
from   t
WHERE  1=2
model
  return updated rows
  partition by ( group_key )
  dimension by ( row_number() over (partition by group_key order by val) as position )
  measures     ( cast( val as varchar2(65) ) as string  )
  rules
    upsert
    iterate( 6 )
      until ( presentv(string[iteration_number+2],1,0) = 0 )
    ( string[0] = string[0] || ',' || string[iteration_number+1] )
order by group_key, position ;

no rows selected