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
3128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
