Rows to String

Tom Kyte's STRAGG

This tutorial shows how to collect values from multiple rows into a single, comma delimited string. This is also known as "string aggregation". It takes values like these

GROUP_KEY  VAL
---------- ----------
Group 3    a
Group 3    b
Group 3    c
 

and yields a string like this.

GROUP_KEY  STRING
---------- ---------------
Group 3    a,b,c
 

A technique originally published by Tom Kyte at Ask Tom "Function to concatenate output" employing a user-defined function called STRAGG will be demonstrated. Other tutorials in this section discuss alternate approaches.

While Tom Kyte himself says he no longer uses STRAGG (see The Tom Kyte Blog:Stringing them up...) some people still prefer it over other solutions. STRAGG is also a good case study for anyone interested in using the Extensibility Framework to write their own aggregate functions.

Prerequisites

create or replace type stragg_type as object
(
  string varchar2(4000),

  static function ODCIAggregateInitialize
    ( sctx in out stragg_type )
    return number ,

  member function ODCIAggregateIterate
    ( self  in out stragg_type ,
      value in     varchar2
    ) return number ,

  member function ODCIAggregateTerminate
    ( self        in  stragg_type,
      returnvalue out varchar2,
      flags in number
    ) return number ,

  member function ODCIAggregateMerge
    ( self in out stragg_type,
      ctx2 in     stragg_type
    ) return number
);
/

create or replace type body stragg_type
is

  static function ODCIAggregateInitialize
  ( sctx in out stragg_type )
  return number
  is
  begin

    sctx := stragg_type( null ) ;

    return ODCIConst.Success ;

  end;

  member function ODCIAggregateIterate
  ( self  in out stragg_type ,
    value in     varchar2
  ) return number
  is
  begin

    self.string := self.string || ',' || value ;

    return ODCIConst.Success;

  end;

  member function ODCIAggregateTerminate
  ( self        in  stragg_type ,
    returnvalue out varchar2 ,
    flags       in  number
  ) return number
  is
  begin

    returnValue := ltrim( self.string, ',' );

    return ODCIConst.Success;

  end;

  member function ODCIAggregateMerge
  ( self in out stragg_type ,
    ctx2 in     stragg_type
  ) return number
  is
  begin

    self.string := self.string || ctx2.string;

    return ODCIConst.Success;

  end;

end;
/

create or replace function stragg
  ( input varchar2 )
  return varchar2
  deterministic
  parallel_enable
  aggregate using stragg_type
;
/
 

Test Data

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

set null "(null)"

break on group_key skip 1 duplicates

select * from t order by group_key, val ;
 
GROUP_KEY  VAL
---------- ----------
Group 1    a

Group 2    a
Group 2    b

Group 3    a
Group 3    b
Group 3    c

Group 4    a
Group 4    a
Group 4    b
Group 4    b

Group 5    a
Group 5    b
Group 5    d
Group 5    e
Group 5    (null)

Group 6    (null)

Group 7    (null)
Group 7    (null)

Group 8    a
Group 8    a
Group 8    b
Group 8    b
Group 8    (null)
Group 8    (null)

(null)     (null)
 

Aggregate Solution

select
  group_key ,
  STRAGG( VAL ) as string
from
  t
group by
  group_key
;
 
GROUP_KEY  STRING
---------- ---------------
Group 1    a

Group 2    a,b

Group 3    a,b,c

Group 4    a,b,b,a

Group 5    a,b,d,e

Group 6    (null)

Group 7    (null)

Group 8    a,a,b,b

(null)     (null)
 

It is important to note that, unlike some other solutions we have seen, these results do not include empty values in the strings for Groups 5 and 8. As well, the string for "Group 7" only contains one null, not two. This is because STRAGG, and all other aggregate functions except COUNT(*) and GROUPING, ignore null values.

To only see distinct values in our result strings we can add the DISTINCT argument to the STRAGG function call. Note how the results for Groups 4 and 8 below differ from those above.

select
  group_key ,
  stragg( DISTINCT val ) as string
from
  t
group by
  group_key
;
 
GROUP_KEY  STRING
---------- ---------------
Group 1    a

Group 2    a,b

Group 3    a,b,c

Group 4    a,b

Group 5    a,b,d,e

Group 6    (null)

Group 7    (null)

Group 8    a,b

(null)     (null)
 

Analytic Version

STRAGG can also be used as an analytical function without changing any of the underlying code.

break on group_key skip 1 duplicates

select
  group_key ,
  val ,
  stragg( val ) OVER
    (
      PARTITION BY GROUP_KEY
      ORDER BY VAL
      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
    as string
from
  t
;
 
GROUP_KEY  VAL        STRING
---------- ---------- ---------------
Group 1    a          a

Group 2    a          a,b
Group 2    b          a,b

Group 3    a          a,b,c
Group 3    b          a,b,c
Group 3    c          a,b,c

Group 4    a          a,a,b,b
Group 4    a          a,a,b,b
Group 4    b          a,a,b,b
Group 4    b          a,a,b,b

Group 5    a          a,b,d,e
Group 5    b          a,b,d,e
Group 5    d          a,b,d,e
Group 5    e          a,b,d,e
Group 5    (null)     a,b,d,e

Group 6    (null)     (null)

Group 7    (null)     (null)
Group 7    (null)     (null)

Group 8    a          a,a,b,b
Group 8    a          a,a,b,b
Group 8    b          a,a,b,b
Group 8    b          a,a,b,b
Group 8    (null)     a,a,b,b
Group 8    (null)     a,a,b,b

(null)     (null)     (null)
 

To change the sort order of values in the string simply change the ORDER BY VAL clause.

break on group_key skip 1 duplicates

select
  group_key ,
  val ,
  stragg( val ) OVER
    (
      partition by group_key
      order by val DESC
      range between unbounded preceding and unbounded following
    )
    as string
from
  t
;
 
GROUP_KEY  VAL        STRING
---------- ---------- ---------------
Group 1    a          a

Group 2    b          b,a
Group 2    a          b,a

Group 3    c          c,b,a
Group 3    b          c,b,a
Group 3    a          c,b,a

Group 4    b          b,b,a,a
Group 4    b          b,b,a,a
Group 4    a          b,b,a,a
Group 4    a          b,b,a,a

Group 5    (null)     e,d,b,a
Group 5    e          e,d,b,a
Group 5    d          e,d,b,a
Group 5    b          e,d,b,a
Group 5    a          e,d,b,a

Group 6    (null)     (null)

Group 7    (null)     (null)
Group 7    (null)     (null)

Group 8    (null)     b,b,a,a
Group 8    (null)     b,b,a,a
Group 8    b          b,b,a,a
Group 8    b          b,b,a,a
Group 8    a          b,b,a,a
Group 8    a          b,b,a,a

(null)     (null)     (null)
 
clear breaks
 

References




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-11591.html]SQL Snippets: Rows to String - Tom Kyte's STRAGG[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11591.html">SQL Snippets: Rows to String - Tom Kyte's STRAGG</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Rows to String - Tom Kyte's STRAGG
  • URL (href): http://www.sqlsnippets.com/en/topic-11591.html