SQL Tips & Techniques- String Aggregation(Contd.)

In my last post, I showed two ways to perform string aggregation. Both of them used recursive SQL. I also mentioned that a built-in function to perform string aggregation would be preferred. Though, I have not found a direct function for string aggregation, one of the comments on my previous post suggested the use of XMLAGG function. In fact, Serge Rielau used the XMLAGG function for string aggregation in his presentation “SQL on Fire! Part 1”. Let’s look at the query using XMLAGG function to perform the string aggregation.

Listing 5

SELECT category, SUBSTR(names, 1, LENGTH(names) -2) as list FROM (SELECT category, REPLACE (REPLACE (XMLSERIALIZE (CONTENT XMLAGG(XMLELEMENT(NAME a, name) ORDER BY name) AS VARCHAR(50)), '<A>', ''), '</A> ', ', ') AS names FROM mytab1 GROUP BY category) AS X CATEGORY LIST ----------- ------------------------------------------------- 1 abc, bcd 2 cde, def, efg 3 fgh, ghi 3 record(s) selected.

In the above query, first we are converting each name into an XML expression using XMLELEMENT function. Here, XMLELEMENT function will add the tags <A> and </A> around each name. After the aggregation, we get rid of these tags using REPLACE function. Though, XMLAGG function is also a work around for string aggregation, I would prefer it over recursive SQL because it’s easier to understand and in my test it had lower estimated cost. Ideally, what we want is a function similar to myConcat as shown below.

Listing 6

SELECT category, myConcat(name, ‘, ’) as list FROM mytab1 GROUP BY category CATEGORY LIST ----------- ------------------------------------------------- 1 abc, bcd 2 cde, def, efg 3 fgh, ghi 3 record(s) selected.

Is that something in your wish list also?