Listagg two columns + group them when same values are shared

Question

I'm working on Oracle SQL-Developer.

Let's start with data to better describe my issue. I have a table with 2 columns

USER
JOB

The relationship between those 2 is 1,n. So 1 user can relate to 1 or more JOB and 1 JOB be related to 1 or more USER.

Here a sample of the data :

The idea is to retrieve interconnected values all together. First of all, I though about LISTAGG that can concatenate and group values. But I'm obtaining this kind of result:

 USER  JOB 
  1     A, B, C, D, E     
  2     B, C, F, G
  3     A, B, C, D
  4     I, J, K, L

OR

USER        JOB 
  1, 3        A     
  1, 2, 3     B      
  1, 2, 3     C      
  1, 3        D      
  1           E      
  2           F
  2           G
  4           I
  4           J
  4           K
  4           L

The desired output is the following:

USER        JOB
1, 2, 3     A, B, C, D, E, F, G
4           I, J, K, L

The idea is that if a user shares one job with someone else, then all jobs are linked all together even if all JOBS are not the same.

Example: USER_1 has 2 jobs A and B. USER_2 has 2 jobs B and C. Then USER_1 and USER_2 share jobs A, B, C.

Reversely, if a job shares one user with another job, then all users are linked all together even if all USERS are not the same...

So I've tried self join in order to retrieve all job for one given user and all users for one job given in order to display all possible shared values, but maybe it's the wrong way because I'm still blocked with LISTAGG. I've thought about PIVOT too but not tried it yet.

I hope I made this clear. What do you all think?

user_1509264 · Answer

This is not going to be efficient for large data sets but you can use a hierarchical query to find the groups each belongs to and then find the distinct users/jobs in each group and then aggregate:

WITH groups ( grp, "USER", job ) AS (
  SELECT MIN(CONNECT_BY_ROOT("USER")) AS grp,
         "USER",
         JOB
  FROM   table_name
  CONNECT BY NOCYCLE
         PRIOR "USER" = "USER"
  OR     PRIOR JOB = JOB
  GROUP BY "USER", JOB
)
SELECT users,
       jobs
FROM   (
         SELECT grp,
                LISTAGG("USER", ',') WITHIN GROUP (ORDER BY "USER") AS users
         FROM   (
           SELECT DISTINCT grp, "USER"
           FROM   groups
         )
         GROUP BY grp
       ) u
       INNER JOIN
       (
         SELECT grp,
                LISTAGG(JOB, ',') WITHIN GROUP (ORDER BY JOB) AS jobs
         FROM   (
           SELECT DISTINCT grp, job
           FROM   groups
         )
         GROUP BY grp
       ) j
       ON ( u.grp = j.grp );

Which, for your sample data:

CREATE TABLE table_name ( "USER", JOB ) AS
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 1, 'B' FROM DUAL UNION ALL
SELECT 1, 'C' FROM DUAL UNION ALL
SELECT 1, 'D' FROM DUAL UNION ALL
SELECT 1, 'E' FROM DUAL UNION ALL
SELECT 2, 'B' FROM DUAL UNION ALL
SELECT 2, 'C' FROM DUAL UNION ALL
SELECT 2, 'F' FROM DUAL UNION ALL
SELECT 2, 'G' FROM DUAL UNION ALL
SELECT 3, 'A' FROM DUAL UNION ALL
SELECT 3, 'B' FROM DUAL UNION ALL
SELECT 3, 'C' FROM DUAL UNION ALL
SELECT 3, 'D' FROM DUAL UNION ALL
SELECT 4, 'I' FROM DUAL UNION ALL
SELECT 4, 'J' FROM DUAL UNION ALL
SELECT 4, 'K' FROM DUAL UNION ALL
SELECT 4, 'L' FROM DUAL;

Outputs:

USERS JOBS

1,2,3 A,B,C,D,E,F,G

4 I,J,K,L

db<>fiddle here

user_4808122 · Answer

Well this is not an answer for Oracle SQL-Developer, but may point you out in an other direction that works fine even if you see that the pure SQL solution hits a performance wall for larger data (my experience).

I'm using R and the library igraph to solve the problem of graph partitioning.

To get the data in R is somehow similar to SQL*Plus (skipping the details of the RJDBC installation and connection setup).

df <-  dbGetQuery(jdbcConnection, 
"select * from table_name")

> df
   USER JOB
1     1   A
2     1   B
3     1   C
4     1   D
5     1   E
...

Now load the data in the undirected graph and visualize it

gg <- graph.edgelist(as.matrix(df), directed=F)
plot(gg)

Now perform the partitioning - basically showing the connected subgraphs from above

> split(V(gg)$name, clusters(gg)$membership)
$`1`
 [1] "1" "A" "B" "C" "D" "E" "2" "F" "G" "3"

$`2`
[1] "4" "I" "J" "K" "L"

This is not the exact result as the users and jobs must be splitted but this should not be the major problem.

Listagg two columns + group them when same values are shared

2 Answers