This tutorial shows how to collect values from multiple rows into a single, XML 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 <V>a</V>
<V>b</V>
<V>c</V>
Oracle's XML features will be used to achieve this result.
First, let's take a look at the 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)
Now here is the solution. The built-in function XMLAGG and the XMLTYPE method GETSRTINGVAL() do most of the work for us.
clear breaks
set recsep each
select
group_key ,
XMLAGG
( XMLTYPE( '<V>' || val || '</V>' )
).GETSTRINGVAL()
as string
from
t
group by
group_key
;
GROUP_KEY STRING
---------- ---------------
Group 1 <V>a</V>
Group 2 <V>a</V>
<V>b</V>
Group 3 <V>a</V>
<V>b</V>
<V>c</V>
Group 4 <V>a</V>
<V>b</V>
<V>b</V>
<V>a</V>
Group 5 <V>a</V>
<V>b</V>
<V/>
<V>e</V>
<V>d</V>
Group 6 <V/>
Group 7 <V/>
<V/>
Group 8 <V>a</V>
<V>b</V>
<V>b</V>
<V/>
<V/>
<V>a</V>
(null) <V/>
Alternatively, we could use the XMLELEMENT function to convert each VAL to an XML element. Unlike the last snippet, using XMLELEMENT results in a string with no line feeds between elements.
column string format a50
select
group_key ,
XMLAGG
( XMLELEMENT( "V", val )
).GETSTRINGVAL()
as string
from
t
group by
group_key
;
GROUP_KEY STRING ---------- -------------------------------------------------- Group 1 <V>a</V> Group 2 <V>a</V><V>b</V> Group 3 <V>a</V><V>b</V><V>c</V> Group 4 <V>a</V><V>b</V><V>b</V><V>a</V> Group 5 <V>a</V><V>b</V><V></V><V>e</V><V>d</V> Group 6 <V></V> Group 7 <V></V><V></V> Group 8 <V>a</V><V>b</V><V>b</V><V></V><V></V><V>a</V> (null) <V></V>
If the resulting string will be over 4000 characters long, we can use GETCLOBVAL() in place of GETSTRINGVAL().
column string format a15
select
group_key ,
xmlagg
( xmltype( '<V>' || val || '</V>' )
).GETCLOBVAL()
as string
from
t
group by
group_key
;
GROUP_KEY STRING
---------- ---------------
Group 1 <V>a</V>
Group 2 <V>a</V>
<V>b</V>
Group 3 <V>a</V>
<V>b</V>
<V>c</V>
Group 4 <V>a</V>
<V>b</V>
<V>b</V>
<V>a</V>
Group 5 <V>a</V>
<V>b</V>
<V/>
<V>e</V>
<V>d</V>
Group 6 <V/>
Group 7 <V/>
<V/>
Group 8 <V>a</V>
<V>b</V>
<V>b</V>
<V/>
<V/>
<V>a</V>
(null) <V/>
To control the sort order of values within the string we can use XMLAGG's ORDER BY clause.
select
group_key ,
xmlagg
( xmltype( '<V>' || val || '</V>' )
ORDER BY VAL DESC
).getStringVal()
as string
from
t
group by
group_key
;
GROUP_KEY STRING
---------- ---------------
Group 1 <V>a</V>
Group 2 <V>b</V>
<V>a</V>
Group 3 <V>c</V>
<V>b</V>
<V>a</V>
Group 4 <V>b</V>
<V>b</V>
<V>a</V>
<V>a</V>
Group 5 <V/>
<V>e</V>
<V>d</V>
<V>b</V>
<V>a</V>
Group 6 <V/>
Group 7 <V/>
<V/>
Group 8 <V/>
<V/>
<V>b</V>
<V>b</V>
<V>a</V>
<V>a</V>
(null) <V/>
Unlike prior aggregate function solutions we have explored XMLAGG does not support the DISTINCT keyword.
select
group_key ,
xmlagg
( xmltype( DISTINCT '<V>' || val || '</V>' )
).getStringVal()
as string
from
t
group by
group_key
;
( xmltype( DISTINCT '<V>' || val || '</V>' )
*
ERROR at line 4:
ORA-30482: DISTINCT option not allowed for this function
To get distinct values we can instead use an inline view. We can also use an inline view to filter out null values.
select
group_key ,
xmlagg
( xmltype( '<V>' || val || '</V>' )
order by val
).getStringVal()
as string
from
( SELECT DISTINCT GROUP_KEY, VAL -- produces distinct values
FROM T
WHERE VAL IS NOT NULL -- filters our null values
) tb
group by
group_key
;
GROUP_KEY STRING
---------- ---------------
Group 1 <V>a</V>
Group 2 <V>a</V>
<V>b</V>
Group 3 <V>a</V>
<V>b</V>
<V>c</V>
Group 4 <V>a</V>
<V>b</V>
Group 5 <V>a</V>
<V>b</V>
<V>d</V>
<V>e</V>
Group 8 <V>a</V>
<V>b</V>