View auf Tabelle mit mehreren Daten im Feld

[bonzai]

Hallo,

vorhanden ist folgende Tabelle:
Name Buch
Huber B1;B2;B3
Meier B4;B5

Nun muss ich eine View erstellen, die mir die Daten so ausgibt:
Name Buch
Huber B1
Huber B2
Huber B3
Meier B4
Meier B5

Ich habe es schon mit einer Split-Funktion probiert, aber irgendwie
komm ich nicht wirklich weiter.
Kann mich hier jemand unterstützen?

Grüße
bonzai

[jum]

wenn man an den String str vorn und hinten noch ein ';' anhängt, dann vielleicht etwa so wie im Beispiel:

with a as (select ';B1;B2;B3;' str from dual)
select str,a1,a2,substr(str,a1+1,a2-a1-1) teil from (select rownum, str, instr(str,';',1,rownum) a1, instr(str,';',1,rownum+1) a2 from a connect by level <= 9)
where a2>0

Man findet das n-te und n+1-te Auftreten des Semikolon in str und schneidet entsprechend aus.
Viel Erfolg

[dimitri]

Naja ich fürchte da muss man ein bissl mehr tun.
Wer so ein Datenmodell macht gehört eigentlich geteert und gefedert.

Ich hab das mal bei mir nachgestellt:

create table tab (name varchar2(20),buch varchar2(40));
insert into tab values('Huber','B1;B2;B3');
insert into tab values('Meier','B4;B5');
CREATE OR REPLACE TYPE MYTYPE AS OBJECT (
    name varchar2(20) ,
    buch varchar2(40)
);

create or replace type MYTYPE_tab is table of mytype;
CREATE OR REPLACE FUNCTION  csv2table (p_input SYS_REFCURSOR)RETURN MYTYPE_tab PIPELINED IS
 l_elementCount BINARY_INTEGER;
 l_elements DBMS_UTILITY.lname_array;
 type l_temp_table_type is table of tab%rowtype; 
 l_temp l_temp_table_type;
BEGIN

 LOOP
   FETCH p_input BULK COLLECT INTO l_temp LIMIT 100;
   EXIT WHEN l_temp.COUNT=0;
   
   FOR k IN l_temp.FIRST .. l_temp.LAST LOOP
     DBMS_UTILITY.COMMA_TO_TABLE(REPLACE(l_temp(k).buch,';',','),l_elementcount,l_elements);     
     FOR i IN 1 .. l_elementCount LOOP
      PIPE ROW(MYTYPE(l_temp(k).name,l_elements(i)));
     END LOOP;
   END LOOP;
 END LOOP;
 
 RETURN;
END;
/

Die Benamsungen der Variablen/Objekte sind vielleicht nicht der Weisheit letzter Schluss aber naja...
Und jetzt der Aufruf:

SELECT * FROM TABLE( csv2table( CURSOR(SELECT * FROM tab)) );

NAME                 BUCH                                     
-------------------- ---------------------------------------- 
Huber                B1                                       
Huber                B2                                       
Huber                B3                                       
Meier                B4                                       
Meier                B5                                       

5 rows selected

Dim

[bonzai]

Danke Dim, funktioniert hervorragend!
Das Datenmodell ist nicht auf meinem Mist gewachsen. Ist von einer
großen Firma mit zwei Buchstaben ;-)

Leider bin ich in Funktionen noch nicht wirklich fit. Kanns Du mir evtl.
noch helfen, wie ich die Funktion allgemeiner Abbilden kann?
Würd das ganze gern auch für andere Ausgangstabellen verwenden,
die mehr als zwei Spalten haben, aber an die Funktion trotzdem
nur zwei Spalten übergeben.

Grüße bonzai

[dimitri]

Hi,

das kommt drauf an, was Du genau ändern möchtest. Was an die Funktion übergeben wird, legst Du über das SQL fest:

SELECT * FROM TABLE( csv2table( CURSOR(SELECT * FROM tab)) );

Die Struktur muss hiermit übereinstimmen:

type l_temp_table_type is table of tab%rowtype;

Denn in diese Struktur lade ich beim FETCH die Daten in 100er Blöcken. Evtl. musst Du vorher einen eigenen Record anlegen, wenn Du das auf bestimmte Spalten und nicht wie hier auf die komplette tabelle, einschränken willst.

Die SQL Typen die ich vor der Funktion angelegt habe liefern die gewünschte Ausgabestruktur und müssen dann evtl. auch noch angepasst werden.

Beachte aber, dass soetwas eine sehr langsame Art und Weise ist die man nur machen sollte, wenn es eben nicht anders geht.

Dim

[ralfb]

Hallo,

noch ein Versuch ohne pl/sql:

x> WITH daten AS
2 (SELECT 'Huber' name, 'B1;B2;B3' Buch FROM dual
3 UNION ALL
4 SELECT 'Meier', 'B4;B5' FROM dual)
5 SELECT DISTINCT name
6 ,substr(buch
7 ,INSTR(buch,';',1,LEVEL)+1
8 ,INSTR(buch,';',1,LEVEL+1)-INSTR(buch,';',1,LEVEL)-1) AS buch
9 FROM (SELECT name, ';'||buch||';' buch,
10 LENGTH (buch)-LENGTH(REPLACE(buch,';',''))+1 laenge FROM daten)
11 CONNECT BY LEVEL <= laenge
12 ORDER BY name,buch
13 ;

NAME BUCH
Huber B1
Huber B2
Huber B3
Meier B4
Meier B5

Gruß

Ralf

< Edit
Die Lösung ist leider nicht ausgereift und sollte nicht angewendet werden!!
(s. Beitrag weiter hinten)
/Edit>

[dimitri]

Die Lösung wär dann sicherlich vorzuziehen.
Kann ich Dein Sql in einem kleinen Artikel auf SQL-Tips.de verwenden?

Dim

[ralfb]

Klar, aber vielleicht mit dem Hinweis, dass die Ursprungsidee natürlich von Toms Seite stammt ;-)

Ralf

[dimitri]

Ach so

Dim

[ralfb]

Da fast alles, was ich über Oracle weiß, von dort oder aus seinen Büchern stammt, habe ich's bei der ersten Antwort nicht extra erwähnt. (Meine "Datenbank" davor hieß dBase!) Und ein bisschen Anpassungsaufwand habe ich ja auch betrieben, weil die Lösung, die ich rumliegen hatte, von einem Parameterstring handelte, der zu splitten war ;-)

Ralf

[dimitri]

, von dort oder aus seinen Büchern stammt,

Ja so hab ich vor 6 Jahren auch angefangen. Sollte eigentlich Pflichtlektüre sein bevor man an eine Oracle Instance darf.

Dim

[ralfb]

Irgendwo in der Größenordnung wird's wohl bei mir auch sein, damals mit ora 7.

Von Tom habe ich auch gelernt, dass man erst mal was testen soll, bevor man's auf die Menschheit los lässt. Deswegen würde ich wohl meine Lösung mal leise zurückziehen wollen, denn bei einer etwas größeren Satzanzahl scheint das connect by so viele Datensätze zu erzeugen, dass wohl alle Speicher überlaufen :-(

Vielleicht fällt mir oder anderen dazu noch was ein. Bis dahin muss man von der Anwendung abraten!

Ralf

[ralfb]

Guten Morgen,

jetzt ist mir noch so etwas eingefallen.

WITH daten AS
  (SELECT 'Huber' name, 'B1;B2;B3' Buch FROM dual
    UNION ALL
   SELECT 'Meyer', 'B4;B5' FROM dual
    UNION ALL
   SELECT 'Meier', 'B4;B5' FROM dual
    UNION ALL
   SELECT 'Maier', 'B4;B5' FROM dual
    UNION ALL
   SELECT 'Paul', 'a1;a2;a3;a4;a5;a6' FROM dual
  )
 ,daten2 AS (SELECT name, ';'||buch||';' buch,
   6 laenge
-- MAX (LENGTH (buch)-LENGTH(REPLACE(buch,';',''))+1) OVER () laenge 
    FROM daten)
SELECT DISTINCT name
      ,substr(buch
        ,INSTR(buch,';',1,lev)+1
        ,INSTR(buch,';',1,lev+1)-INSTR(buch,';',1,lev)-1) AS buch
FROM (SELECT d.name,d.buch,d.laenge,u.lev 
      FROM daten2 d,
          (SELECT LEVEL lev FROM dual 
           CONNECT BY  LEVEL <= 6
--                             (SELECT MAX(laenge) FROM daten2)
          ) u)
WHERE INSTR(buch,';',1,lev+1)-INSTR(buch,';',1,lev)-1 >0
ORDER BY name,buch
;

Ich habe in dem Code mal eine 6 für die max. Anzahl der Werte je Zeile eingegeben. Wenn die nicht bekannt ist, könnte man auch statt dessen den jeweils auskommentierten Teil verwenden.

Gruß

Ralf

[jum]

@ralfb der Kern ist übrigens absolut identisch mit der Antwort #2

[ralfb]

@ralfb der Kern ist übrigens absolut identisch mit der Antwort #2

@jum - damit hast du natürlich recht. Ist doch nicht schlimm - oder

Weil mir mein Code durch das Max-Getue noch nicht ganz so ausgereift erschien (und ich jetzt auch mal das table(cast(multiset-Konstrukt richtig verstanden habe ), hier noch eine neue Variante:

WITH daten AS
  (SELECT 'Huber' name, 'B1;B2;B3' Buch FROM dual
    UNION ALL
   SELECT 'Meyer', 'B4;B5' FROM dual
    UNION ALL
   SELECT 'Meier', 'B4;B5' FROM dual
    UNION ALL
   SELECT 'Maier', 'B4;B5' FROM dual
    UNION ALL
   SELECT 'Paul', 'a1;a2;a3;a4;a5;a6' FROM dual
  )
SELECT DISTINCT d.name
      ,substr(d.buch
        ,INSTR(d.buch,';',1,u.column_value)+1
        ,INSTR(d.buch,';',1,u.column_value+1)   -INSTR(d.buch,';',1,u.column_value)-1) 
            buch
FROM (SELECT name,';'||buch||';' buch FROM daten) d
    ,TABLE(CAST(MULTISET(SELECT LEVEL FROM dual 
                         CONNECT BY  LEVEL <= 
                           LENGTH(d.buch)-LENGTH(REPLACE(d.buch,';',''))-1)
                AS sys.odcinumberlist)) u
WHERE INSTR(d.buch,';',1,u.column_value+1)-INSTR(d.buch,';',1,u.column_value)-1 >0
ORDER BY name,buch
;

Gruß

Ralf