SQL String Aggregation Techniques

Tuesday, January 10, 2017

SQL String Aggregation Techniques

A use case I have encountered in more than one project concerns aggregating Strings from a database. My experience on these projects is that it turns out this is often not common knowledge, so I thought I’d share some examples for different RDBMS.

Consider an example such as the following:

Field 1 Field 2
1 A
1 B
2 A

 

Where the desired output would look something like this:

1    AB
A

 

Where Field 1 and 2 could be replaced for example by department and employees, or client and accounts.

Oracle

Starting from Oracle 11g Release 2 you could run the following:

SELECT FIELD1, LISTAGG(FIELD2, ‘,’) WITHIN GROUP (ORDER BY FIELD1) AS aggregation
FROM TABLENAME
GROUP BY FIELD1;

The LISTAGG function was introduced in 11g R2 and makes it quite easy to aggregate strings and order them.

If you are running an older version of Oracle but you have the WM_CONCAT function present you could do this:

SELECT FIELD1, WM_CONCAT(FIELD2) as aggregation
FROM TABLENAME
GROUP BY FIELD1;

But this is not a supported function and might not be present for you. If neither of these two options applies for you, it’s also possible to create your own aggregation function. However, there is also a statement you could use that would allow you to achieve the same thing without having to write PL/SQL:

SELECT
    FIELD1, LTRIM(MAX(SYS_CONNECT_BY_PATH(FIELD2, ‘,’))
    KEEP (DENSE_RANK LAST ORDER BY curr), ‘,’) AS aggregation
FROM (SELECT    FIELD1,
        FIELD2,
        ROW_NUMBER() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) AS curr,
        ROW_NUMBER() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) -1 AS prev
    FROM TABLENAME)
GROUP BY FIELD1
CONNECT BY prev = PRIOR curr AND FIELD1 = PRIOR FIELD1
START WITH curr=1;

While this may seem complicated, when in a situation where you do not have access to create PL/SQL functions and find yourself on a version before 11G R2 it can work wonders.

SQL Server

In SQL Server 2016 you can simply run:

SELECT t1.FIELD1, STRING_AGG (t2.FIELD2, ‘,’) AS aggregation
FROM TABLENAME t1
JOIN TABLENAME t2 on t1.id = t2.id
GROUP BY t1.FIELD1;

On older versions you can use:

SELECT FIELD1, STUFF((
        SELECT ‘,’ + FIELD2
        FROM TABLENAME t1
        WHERE t2.FIELD1 = t1.FIELD1 FOR XML PATH(‘’)),1,1,’’) AS aggregation
FROM TABLENAME t2
GROUP BY FIELD1;

MySQL

MySQL has a built-in function GROUP_CONCAT which looks like:

SELECT FIELD1, GROUP_CONCAT(FIELD2) AS aggregation
FROM TABLENAME
GROUP BY FIELD1
ORDER BY FIELD1;

 

/ND