This tutorial shows how to collect values from multiple rows into a single, CSV 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 one.
GROUP_KEY STRING ---------- --------------- Group 3 a,b,c
Oracle's XML features will be used to achieve this result.
In the XML Method tutorial we saw how to use the XMLAGG and XMLELEMENT functions to create an XML delimited string like this.
column string format a62
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>
By adding calls to EXTRACT and LTRIM we can convert this solution into one that gives us comma separated values.
select
group_key ,
LTRIM
( EXTRACT
( xmlagg( xmlelement( "V", ',' || val ) ),
'/V/text()'
) ,
','
) 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,,e,d Group 6 (null) Group 7 (null) Group 8 a,b,b,,,a (null) (null)
To control the sort order of values within the string we can use XMLAGG's ORDER BY clause.
select
group_key ,
ltrim
( extract
( xmlagg
( xmlelement( "V", ',' || val )
ORDER BY VAL DESC
),
'/V/text()'
) ,
','
) as string
from
t
group by
group_key
;
GROUP_KEY STRING ---------- -------------------------------------------------------------- Group 1 a Group 2 b,a Group 3 c,b,a Group 4 b,b,a,a Group 5 e,d,b,a Group 6 (null) Group 7 (null) Group 8 b,b,a,a (null) (null)
As with the original solution, you can use a FROM clause subquery to produce distinct values and filter out null values if you need to.
select
group_key ,
ltrim
( extract
( xmlagg
( xmlelement( "V", ',' || val )
order by val
),
'/V/text()'
) ,
','
) 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 a Group 2 a,b Group 3 a,b,c Group 4 a,b Group 5 a,b,d,e Group 8 a,b
Acknowledgements
These solutions were adapted from one by OTN user "micheals" at OTN SQL and PL/SQL Forum - Re: Return multi-row result as one comma-delimited row.
See Also
An alternate solution by OTN user Aketi Jyuuzou is available at OTN SQL and PL/SQL Forum - Re: how to display empno in a single row instead of displaying it vertically