spikeTJ spikeTJ - 4 months ago 13
SQL Question

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

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;

Answer

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;