===== Ein Listen von Werten in SQL erstellen =====
{{ :images:piombino_toskana.png?direct |Piombino in der Toskana}}
Aufgabe: Ein separierte Liste soll aus den Werten einer SQL Abfrage erstellt werden.
==== 11g Lösung mit listagg ====
Ab 11g kann das gut mit einer [[prog:oracle_analytic_functions|ANA Funktion]] gelöst werden.
SQL>select deptno
, listagg(ename,':') within group (order by ename) as emp_list
from emp group by deptno
/
DEPTNO EMP_LIST
---------- ---------------------------------
10 CLARK:KING:MILLER
20 ADAMS:JONES
30 ALLEN:MARTIN:TURNER:WARD
Mit listagg kann eine Liste bis maximal 4000 Zeichen aggregiert werden.
Quelle, siehe http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm und http://www.oracle-developer.net/display.php?id=515
----
==== Ab Oracle 12r2 - LISTAGG overflow ====
Ab der Version 12c R2 kann auf den ärgerlichen "ORA-01489: result of string concatenation is too long" Fehler reagiert werden.
SQL>select deptno
, listagg(ename,':') within group (order by ename) on overflow truncate '...' with count as emp_list
from emp group by deptno
/
DEPTNO EMP_LIST
---------- ---------------------------------
10 CLARK:KING:MILLER
20 ADAMS:JONES
30 ALLEN:MARTIN:TURNER:WARDxxxxx ... (100)
Doku => https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466
----
==== Ab Oracle 19- LISTAGG distinct ====
Ab 19c können auch Duplicate in der Liste aussortiert werden!
siehe auch => https://oracle-base.com/articles/19c/listagg-distinct-19c
----
==== Eigene Aggregat Funktion verwenden um einen ORA-01489 zu vermeiden ====
Eine Alternative Lösung ist eine eigene Aggregate Funktion.
siehe dazu => [[prog:sql_aggregatfunktion_erstellen|Eine eigene Aggregat Funktion erstellen]]
----
==== SYS.STRAGG verwenden ====
Die SYS Funktion **STRAGG** kann auch verwendet werden, allerdings wird hier der zuvor ermittelte Wert aller Zielen jeweils pro Zeile mit ausgegeben.
Die Funktion scheint all Input Variable einfach zuvor zu summieren, unabhängig von der aktuellen Zeile.
SELECT deptno
, sys.stragg(ename||';') AS emp_list
FROM scott.emp
where deptno=10
GROUP BY deptno
/
DEPTNO EMP_LIST
------------ --------------------------------------------------
10 CLARK;KING;MILLER;
-- Bei mehr als einer Treffer Zeile wird das Ergebnis mehrdeutig!
SELECT deptno
, sys.stragg(ename||';') AS emp_list
FROM scott.emp
where deptno in (10,20)
GROUP BY deptno
/
DEPTNO EMP_LIST
------------ --------------------------------------------------
10 CLARK;KING;ADAMS;FORD;MILLER;
20 CLARK;KING;ADAMS;FORD;MILLER;
D.h. ein Einsatz ist eher zu vermeiden, zumal das keine offizielle Funktion ist.
----
==== Die Collect Methode ====
Collect Methode:
SQL>select deptno
, collect(ename) as emp_list
from emp
group by deptno
/
DEPTNO EMP_LIST
------ ----------------------------------------------------------------------
10 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('CLARK', 'MILLER', 'KING')
20 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('JONES', 'ADAMS')
30 SYSTPFOeGQC1UQMGz4nUc6QoSaw==('ALLEN', 'TURNER', 'MARTIN', 'WARD')
----
==== Die wmsys.wm_concat Funktion ====
Die wmsys.wm_concat:
SQL>select deptno,wmsys.wm_concat(ename) as emp_list from emp group by deptno;
DEPTNO EMP_LIST
------ ----------------------------------------------------------------------
10 CLARK,MILLER,KING
20 JONES,ADAMS
30 ALLEN,TURNER,MARTIN,WARD
----
==== XML verwenden ====
Daten in XML verwandeln und wieder in Text zerlegen:
SQL>select deptno
, rtrim ( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()'), ',' ) as as emp_list
from emp
group by deptno;
DEPTNO EMP_LIST
------ ----------------------------------------------------------------------
10 CLARK,MILLER,KING
20 JONES,ADAMS
30 ALLEN,TURNER,MARTIN,WARD
----
==== in PL/SQL eine Liste wieder zerlegen ====
Mit Hilfe von **dbms_utility.comma_to_table** lässt sich eine Liste in ein Array transformieren.
Beispiel:
declare
v_tab_length BINARY_INTEGER;
v_s_array DBMS_UTILITY.lname_array;
begin
v_list:='A,B,C,D,E,F';
-- split servicenames in a table
dbms_utility.comma_to_table( list => v_list
, tablen => v_tab_length
, tab => v_s_array);
....
end;
/
siehe auch: [[prog:plsql_string_tokenizer|Oracle PL/SQL - Strings zerlegen]]
----
==== Eine Liste in Ergebniszeilen einer Abfrage umwandeln ====
Mit** sys.dbms_debug_vc2coll** (siehe [[http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_debug.htm#ARPLS66151|Das DBMS_DEBUG Package]]) kann eine Ergebnisliste wieder in Zeilen ausgegeben werden.
Beispiel:
select * from table(sys.dbms_debug_vc2coll('a','b','c','d','e'));
COLUMN_VALUE
--------------
a
b
c
d
e
Kann zum Beispiel für eine Pivot Abfrage verwendet werden.
Gleiches kann auch mit XMLTABLE und einem XMLTYPE erreicht werden:
select *
from xmltable ('/emails/email'
passing xmltype ('12345@12345.ru6789@67890.com')
columns "mail" varchar2 (255) path 'm'
);
mail
-----
12345@12345.ru
6789@67890.com
----
===== SYS.ODCINUMBERLIST ====
Min seit 10g siehe =>Database Data Cartridge Developer's Guide https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcicnstnts.htm
select *
from TABLE
(
SYS.ODCINUMBERLIST(1,2,3,4,5,6,1,2,3,4,5)
) T ;
COLUMN_VALUE
------------
1
2
3
4
....
Alternativ ODCIDATELIST,ODCIVARCHAR2LIST
Nach möglichen Kollections suchen:
SQL> desc sys.odcinumberlist
sys.odcinumberlist VARRAY(32767) OF NUMBER
SQL> desc sys.odcivarchar2list
sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000)
SQL> desc sys.ODCIDATELIST
sys.ODCIDATELIST VARRAY(32767) OF DATE
SQL> desc sys.dbms_debug_vc2coll
sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)
select type_name
, owner
from all_types
where typecode = 'COLLECTION'
and owner != user
/
----
==== Quellen ====
Oracle SQL Dokumenation
Web
* http://www.williamrobertson.net/documents/one_row.html
* http://www.oracle-developer.net/display.php?id=306
* https://modern-sql.com/feature/listagg