Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_liste_erstellen

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:sql_liste_erstellen [2019/12/18 08:54]
gpipperr [Quellen]
prog:sql_liste_erstellen [2019/12/20 10:29] (aktuell)
gpipperr [Ab Oracle 19- LISTAGG distinct]
Zeile 1: Zeile 1:
 +===== 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.
 +<code sql>
 +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
 +
 +</code>
 +
 +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.
 +
 +
 +
 +
 +<code sql>
 +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)
 +
 +</code>
 +
 +
 +
 +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. 
 +
 +<code sql>
 +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;
 + 
 +</code>
 +
 +D.h. ein Einsatz ist eher zu vermeiden, zumal das keine offizielle Funktion ist.
 +
 +----
 +
 +==== Die Collect Methode ==== 
 +
 +Collect Methode:
 +
 +<code sql>
 +
 +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')
 +
 +</code>
 +
 +
 +----
 +
 +==== Die wmsys.wm_concat Funktion ==== 
 +
 +Die wmsys.wm_concat:
 +
 +<code sql>
 +
 +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
 +
 +</code>
 +
 +----
 +
 +
 +==== XML verwenden ==== 
 +
 +Daten in XML verwandeln und wieder in Text zerlegen:
 +<code sql>
 +
 +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
 +
 +</code>
 +
 +----
 +
 +==== 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:
 +<code SQL>
 +
 +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;
 +/
 +
 +</code>
 +
 +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:
 +
 +<code sql>
 +
 +select * from table(sys.dbms_debug_vc2coll('a','b','c','d','e'));
 +
 +COLUMN_VALUE
 +--------------
 +a
 +b
 +c
 +d
 +e
 +
 +
 +</code>
 +
 +Kann zum Beispiel für eine Pivot Abfrage verwendet werden.
 +
 +
 +Gleiches kann auch mit XMLTABLE und einem XMLTYPE erreicht werden:
 +<code sql>
 +
 +select *
 +  from xmltable ('/emails/email'
 +                  passing xmltype ('<emails><email><m>12345@12345.ru</m></email><email><m>6789@67890.com</m></email></emails>')
 +                   columns "mail" varchar2 (255) path 'm'
 + );
 +mail
 +-----
 +12345@12345.ru
 +6789@67890.com
 +
 +</code>
 +
 +
 +----
 +
 +
 +===== SYS.ODCINUMBERLIST ====
 +
 +
 +Min seit 10g siehe =>Database Data Cartridge Developer's Guide https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcicnstnts.htm
 +
 +
 +<code sql>
 +select * 
 +from TABLE
 +   (
 +      SYS.ODCINUMBERLIST(1,2,3,4,5,6,1,2,3,4,5)
 +   ) T ;
 +   
 +
 +COLUMN_VALUE
 +------------
 +           1
 +           2
 +           3
 +           4
 +           ....   
 +
 +</code>
 +
 +Alternativ ODCIDATELIST,ODCIVARCHAR2LIST
 +
 +Nach möglichen Kollections suchen:
 +
 +<code sql>
 +
 +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
 +/
 +
 +</code>
 +
 +----
 +
 +==== 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
 +
  
"Autor: Gunther Pipperr"
prog/sql_liste_erstellen.txt · Zuletzt geändert: 2019/12/20 10:29 von gpipperr