Per capirci supponiamo di avere una tabella cosi formata:
SELECT CUBEID,CUBESETID FROM APPO_GR_TEST;
CUBEID CUBESETID
--------------- ----------
TEST_TAAE0250 TEST_001
TEST_TBDE0050 TEST_001
TEST_TBDE0060 TEST_001
TEST_TBDE0070 TEST_001
TEST_TBDE0080 TEST_001
TEST_TBDE0085 TEST_001
TEST_TBDE0090 TEST_001
TEST_TBDE0230 TEST_001
TEST_TBDE0270 TEST_001
TEST_TBDE0300 TEST_001
TEST_TBDE0310 TEST_001
TEST_TBDE0320 TEST_001
TEST_TBDE0400 TEST_001
TEST_TIAA0100 TEST_001
TEST_TIAA0200 TEST_001
TEST_TIAA0300 TEST_001
TEST_TIAA0400 TEST_001
TEST_TIAA0450 TEST_001
TEST_TIAA0500 TEST_001
Quello che vogliamo orizzontalizzare è il contenuto del campo CUBEID magari mettendo una "," tra un cubo e l'altro. Allora possiamo utilizzare per fare questa orizzontalizzazione diverse funzioni Oracle, di seguito alcune di queste funzioni.
FUNZIONE LISTAGG
Dalla versione 11g è presente una funzione che permette di orizzontalizzare i dati che all'interno di una tabella risultano essere verticalizzati.
Allora per orizzontalizzare eseguiamo la seguente query:
SELECT LENGTH(LISTAGG(CUBEID, ',') WITHIN GROUP (ORDER BY CUBESETID)) LL,
LISTAGG(CUBEID, ',') WITHIN GROUP (ORDER BY CUBESETID) LIST
from APPO_GR_TEST where CUBESETID='TEST_001' ;
LL LIST
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
265 TEST_TAAE0250,TEST_TBDE0050,TEST_TBDE0060,TEST_TBDE0070,TEST_TBDE0080,TEST_TBDE0085,TEST_TBDE0090,TEST_TBDE0230,TEST_TBDE0270,TEST_TBDE0300,TEST_TBDE0310,TEST_TBDE0320,TEST_TBDE0400,TEST_TIAA0100,TEST_TIAA0200,TEST_TIAA0300,TEST_TIAA0400,TEST_TIAA0450,TEST_TIAA0500
Abbiamo ottenuto una stringa concatenata, l'unico inconveniente è però che se la lunghezza della
stringa supera i 4000 caratteri avremo un errore in quanto la funzione ritorna un VARCHAR2 e non un CLOB.
Report error -
Errore SQL: ORA-01489: Il risultato della concatenazione della stringa è troppo lungo
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
FUNZIONE WM_CONCAT
Altra funzione che possiamo utilizzare per orizzontalizzare è stata sviluppata per l'option del db chiamata OWM (Oracle Workspace Manager).
A differenza di quella indicata sopra, questa è presente in oracle dalla versione 10g però deve essere utilizzata con una group by e risulta meno performante rispetto alla precedente inoltre non è possibile modificare il carattere separatore che in questo caso è sempre ",", mentre nella funzione precedente poteva essere qualsiasi cosa.
SELECT CUBESETID, WMSYS.WM_CONCAT(CUBEID) AS LIST
FROM APPO_GR_TEST
GROUP BY CUBESETID;
CUBESETID LIST
------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_001 TEST_TAAE0250,TEST_TIAA0500,TEST_TIAA0450,TEST_TIAA0400,TEST_TIAA0300,TEST_TIAA0200,TEST_TIAA0100,TEST_TBDE0400,TEST_TBDE0320,TEST_TBDE0310,TEST_TBDE0300,TEST_TBDE0270,TEST_TBDE0230,TEST_TBDE0090,TEST_TBDE0085,TEST_TBDE0080,TEST_TBDE0070,TEST_TBDE0060,TEST_TBDE0050
FUNZIONE COLLECT
Dalla versione 10g è presente anche un'altra funzione la COLLECT che però ha un utilizzo un pò più complesso, in quanto occorre prima definire un TYPE.
Vediamo come si utilizza:
CREATE OR REPLACE TYPE varchar2_gr AS TABLE OF VARCHAR2(4000);
TYPE VARCHAR2_GR compilato
SELECT CUBESETID, CAST(COLLECT(CUBEID) AS varchar2_gr) AS LIST
FROM APPO_GR_TEST
GROUP BY CUBESETID;
CUBESETID LIST
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_001 WRES049.VARCHAR2_GR('TEST_TAAE0250','TEST_TIAA0500','TEST_TIAA0450','TEST_TIAA0400','TEST_TIAA0300','TEST_TIAA0200','TEST_TIAA0100','TEST_TBDE0400','TEST_TBDE0320','TEST_TBDE0310','TEST_TBDE0300','TEST_TBDE0270','TEST_TBDE0230','TEST_TBDE0090','TEST_TBDE0085','TEST_TBDE0080','TEST_TBDE0070','TEST_TBDE0060','TEST_TBDE0050')
Altre opzioni per orizzontalizzare il daro sarebbe lo sviluppo di funzioni ma questo comporta quindi l'utilizzo di funzioni non native di oracle.
Alcuni esempi li trovate qui:
https://oracle-base.com/articles/misc/string-aggregation-techniques
Nessun commento:
Posta un commento