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/>
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. We cannot, however, use inline views to control the sort order of values within the string because aggregate functions like XMLAGG do not guarantee to return values in any particular order.
select
group_key ,
xmlagg
( xmltype( '<V>' || val || '</V>' )
).getStringVal()
as string
from
( SELECT DISTINCT GROUP_KEY, VAL -- produces distinct values
FROM T
WHERE VAL IS NOT NULL -- filters our null values
ORDER BY VAL DESC -- attempt to control sort order won't work
) tb
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>a</V>
Group 5 <V>e</V>
<V>d</V>
<V>a</V>
<V>b</V>
Group 8 <V>b</V>
<V>a</V>
