Home / Questions / 520059
Accepted
Henriquez10
Asked: 2022-08-12 12:55:42 +0800 CST 2022-08-12 12:55:42 +0800 CST

How to display the description of a multi-valued table in PL/SQL?

  • 772

I have created a table called seasons

id_estacion estacion 
1 Radio la Que buena
2 Radio Corazón
3 Radio la Chévere

I display these values ​​in a select list (multiple selection) with a station display and return id_station these data are stored in the form_request table

How to do a select from the form_request table and show me the values ​​separated by a comma by station name and not by id_station ?

So far I have this code that only works with single values

SELECT CASE
WHEN ESTACION = '1' THEN (SELECT ESTACION FROM estacion WHERE id_eiexport = '1')
WHEN ESTACION = '2' THEN (SELECT ESTACION FROM estacion WHERE id_eiexport = '2')
WHEN ESTACION = '3' THEN (SELECT ESTACION FROM estacion WHEREid_eiexport = '3')
FROM FORM_REQUEST

I want my select to show(separated by comma) when the person made multiple selection

Radio Corazón, Radio la Chévere
Radio La Que buena, Radio Corazón

But it returns me by id_station this:

id | estacion
_____________
1  | 2, 3
2  | 1, 2
plsql

2 Answers

  1. You can use the listagg function :

    select listagg(estacion, ', ') WITHIN GROUP (ORDER BY estacion) Estaciones
from form_request
group by id

    The Listagg aggregation function returns a string and is used to transform data from multiple rows into a list of values ​​concatenated and separated by a specific delimiter, which in this case is a comma( ',' ). Finally, the group by clause is used to obtain all the stations that have the same id in common.

    References:

    http://mundodb.es/oracle-listagg-function

    https://www.techonthenet.com/oracle/functions/listagg.php

    • 0
  2. Best Answer

    I hope someone finds it useful if you use Oracle Apex, just bind the multiple select list to a query like this:

     select estacion a, estacion b 
 from estacion

    With that, Radio Chereve, Radio Corazón will be saved in the table and not with the id_station 3.2

    • 0