How to displaying multiple rows values in a one line of output

Question

i have this query:

SELECT table_1.user_code AS user, 
   table_1.charge_code,
   table_1.unit_code,
   table_3.roles_code,
   table_2.apps_code
FROM table_1
 INNER JOIN table_3 ON
       table_1.user_code = table_3.Cod_Usr
 INNER JOIN table_2 ON
       table_3.roles_code = table_2.roles_code
WHERE
       table_1.fec_baja IS NULL
ORDER BY table_1.user_code ASC, table_3.roles_code, table_2.roles_code;

I usually use it to know about the applications_code that a user has access according to their charge_code , unit_code and role_code

And, this is the results of the query above:

   user_code   charge_code  unit_code   role_code   apps_code
1   USER01     AAA001       111111111   BASICMENU   APPS0001
2   USER01     AAA001       111111111   BASICMENU   APPS0005
3   USER01     AAA001       111111111   BASICMENU   APPS0008
4   USER01     AAA001       111111111   BASICMENU   APPS1245
5   USER01     AAA001       111111111   LOGIN       APPS0013
6   USER01     AAA001       111111111   LOGIN       APPS1291
7   USER02     BBB0001      222222222   CASHIER001  APPS01SF
8   USER02     BBB0001      222222222   CASHIER001  APPS12RE
9   USER02     BBB0001      222222222   CASHIER001  APPS178E
8   USER02     BBB0001      222222222   CASHIER001  APPSZS45
9   USER02     BBB0001      222222222   CASHIER001  APPS12DF
10  USER02     BBB0001      222222222   CASHIER001  APPS1RE5
11  USER02     BBB0001      222222222   BASICMENU   APPSTY45
12  USER02     BBB0001      222222222   BASICMENU   APPS1KJ5

Previously I was working perfectly, but now takes more than 10 minutes to run due to the large number of records.

I'm trying to get the following result:

    user_code   charge_code  unit_code  role_code   apps_code
1   USER01      AAA001       111111111  BASICMENU   APPS0001,APPS0005,APPS0008,APPS1245
2   USER01      AAA001       111111111  LOGIN       APPS0013,APPS1291
3   USER02      BBB0001      222222222  CASHIER001  APPS01SF,APPS12RE,APPSZS45,APPS178E,APPS12DF,APPS1RE5
4   USER02      BBB0001      222222222  BASICMENU   APPSTY45,APPS1KJ5

Note: These are records showing each table.

The table_1: user_code / charge_code / unit_code
The table_2: role_code / apps_code
The table_3: role_code / user_code

I'm trying to implement a query like this:

SELECT table_2.roles_code, wm_concat(table_2.apps_code) AS apps 
FROM   table_2
GROUP BY table_2.roles_code;

Why do you want the output in that aggregated (comma-separated) format? Perhaps there are more efficient ways to do what you ultimately need to do. — , Aug 01 '16 at 20:49
The output format its not important... I just need the all values in a single row. @mathguy — spikeTJ, Aug 01 '16 at 21:38
It depends on how you want (need) to use the output. Suppose you are able to aggregate as you asked initially - then what are you using that for, or how are you using it? In many cases it becomes the input for further processing, is that the case? Or are you doing this strictly for displaying / reporting purposes? — , Aug 02 '16 at 14:27
Oh, and I see, listagg doesn't work for you... In any case, if your original query (that used to work fine before) now takes a long time, then a new one using listagg will take even longer, and if you have to do listagg "by hand" in Oracle 10, it will take longer still. Were you thinking of this as a way to improve performance? This won't help. Did you consider a materialized view? — , Aug 02 '16 at 14:32

ScaisEdge · Accepted Answer · 2016-08-02T17:04:01.040

You could use LISTAGG

SELECT 
table_2.roles_code, 
LISTAGG(table_2.apps_code, ', ') WITHIN GROUP (ORDER BY table_2.apps_code) "apps code"
FROM table_2
GROUP BY able_2.roles_code

and for your query

SELECT table_1.user_code AS user, 
   table_1.charge_code,
   table_1.unit_code,
   table_3.roles_code,
   table_2.roles_code, 
   LISTAGG(table_2.apps_code, ', ') WITHIN GROUP (ORDER BY table_2.apps_code) "apps code"
FROM table_1
 INNER JOIN table_3 ON
       table_1.user_code = table_3.Cod_Usr
 INNER JOIN table_2 ON
       table_3.roles_code = table_2.roles_code
WHERE
       table_1.fec_baja IS NULL
GROUP BY table_1.user_code ASC, table_3.roles_code, table_2.roles_code
ORDER BY table_1.user_code ASC, table_3.roles_code, table_2.roles_code;

with wm_concat() should be

SELECT table_1.user_code AS user, 
   table_1.charge_code,
   table_1.unit_code,
   table_3.roles_code,
   table_2.roles_code, 
   wm_concat(table_2.apps_code) "apps code"
FROM table_1
 INNER JOIN table_3 ON
       table_1.user_code = table_3.Cod_Usr
 INNER JOIN table_2 ON
       table_3.roles_code = table_2.roles_code
WHERE
       table_1.fec_baja IS NULL
GROUP BY table_1.user_code ASC, table_3.roles_code, table_2.roles_code
ORDER BY table_1.user_code ASC, table_3.roles_code, table_2.roles_code;

I try many times this way, but i got this error: ORA-00923: FROM keyword not found where expected. I have reviewed the syntax and can not find any errors. @scaisEdge — spikeTJ, Aug 01 '16 at 21:42
I have this version of oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi. I think listagg works on 11.2 or superior. @scaisEdge — spikeTJ, Aug 02 '16 at 12:28
Do you know a way to do integrating with a wm_concat function? @scaisEdge — spikeTJ, Aug 02 '16 at 14:27

How to displaying multiple rows values in a one line of output

1 Answers1