domingo, 27 de diciembre de 2015

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.