Grouping Rows in Oracle

Hi,

How many times do you want to show some "properties" of a key value, and the result set means more than a single row?

For instance, you have the following SQL Statement.

In this case, there are 4 rows matching the statement.
Oracle has introduced in the Oracle 11g Release 2 the LISTAGG function which allows us to have the same result set but grouping it in a single row
Here you have how it works:


SELECT ROLEUSER, LISTAGG(ROLENAME, ' - ') 
          WITHIN GROUP (ORDER BY ROLENAME)   AS ROLENAME
FROM PSROLEUSER
WHERE ROLEUSER = 'VP1'    

GROUP BY ROLEUSER

In the example, I am asking for all the ROLENAME values, that match to the GROUP BY clause. I am also ordering it by ROLENAME, and separating them with the " - " character.

Here you have the results:

But unfortunately, I also have bad news.

As I told you, this function only works from version Oracle 11g Release 2 onwards.

If you are running older versions there is also a way of solving it, but it's not documented. So, if you have to use it, do it carefully.

SELECT ROLEUSER, WM_CONCAT(ROLENAME) AS ROLENAME

FROM PSROLEUSER

WHERE ROLEUSER = 'VP1'

GROUP BY ROLEUSER

MySQL

In this language there is also a function that helps us with this item. In this case, it is called GROUP_CONCAT

SELECT ROLEUSER, GROUP_CONCAT(ROLENAME) AS ROLENAME

FROM PSROLEUSER

WHERE ROLEUSER = 'VP1'

GROUP BY ROLEUSER

SQL SERVER

I haven't seen a function in SQL Server similar to these ones. I am afraid in this language we should solve it by using a recursive sub-Query in the SELECT statement, such as:

SELECT A.ROLEUSER

    , (   SELECT  B.ROLENAME + ' - '

          FROM PSROLEUSER B

          WHERE B.ROLEUSER = A.ROLEUSER  

          FOR XML PATH('') ) AS ROLENAME

FROM PSROLEUSER A

WHERE A.ROLEUSER = 'VP1'

GROUP BY A.ROLEUSER

And that's all.

I hope this blog could be so useful to you as it is to me.

Regards.
Facundo Salerno.