Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

SQL Abfrage: 1:N (Tabelle transponieren)

Dieses Thema im Forum "Oracle" wurde erstellt von Heiko, 16 Januar 2015.

  1. Heiko

    Heiko Benutzer

    Hallo Forum,

    gleich voraus: Ich bin zum ersten Mal in Kontakt mit einer Oracle-DB.

    Unser ERP System ist ein wenig - na sagen wir mal - am Anwender vorbei entwickelt. Ich muss deswegen einen Export von Adressendaten bewerkstelligen.

    Das Problem das sich mir dabei stellt ist, dass ich eine (eigentlich 12) Tabellen habe aus denen ich mir diverse Daten zusammen suche. Bis dahin kein Problem. Nun aber stehen sämtliche Kommunikationsarten (also E-Mailadressen, Telefonnummern, Webadressen etc. in einer separaten Tabelle. Ich habe also einen Datensatz in der Tabelle ADRESSE und dazu bis zu 10 Datensätze in der Tabelle KOMMUNIKATION. Ich muss aber, für die Verarbeitung in einem anderen Programm, diese bis zu 10 Datensätze "hinter" die aus der Tebelle ADRESSE bekommen.

    Wie muss das Statement lauten um dies in die Reihe (im wahrsten Sinn des Wortes) zu bekommen.

    Vielen Dank für jede Hilfe
    Gruß
    Heiko
     
  2. akretschmer

    akretschmer Datenbank-Guru

    In PostgreSQL würde ich dazu array_agg() verwenden, evtl. aus dem Array dann noch einen schönen String machen. Prinzipiell sollte das ähnlich in Oraggle gehen. Dein Weg geht also über eine Aggregationsabfrage.
     
  3. Heiko

    Heiko Benutzer

    Danke für die Antwort blauer Elefant - aber das hilft mir nicht. Irgendein Beispiel wäre nett..
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Minimal - Beispiel:

    Code:
    test=*# select * from bla;
      t
    -------
     text1
     text2
     text3
     text4
    (4 rows)
    
    Time: 0,191 ms
    test=*# select array_agg(t) from bla;
      array_agg
    ---------------------------
     {text1,text2,text3,text4}
    (1 row)
    
    Time: 0,208 ms
    test=*# select array_to_string(array_agg(t),', ') from bla;
      array_to_string
    ----------------------------
     text1, text2, text3, text4
    (1 row)
    
    
     
  5. Distrilec

    Distrilec Datenbank-Guru

    Davon ausgehend das die Tabellen gleich aufgebaut sind und du mit "hinter" eig. "unter" meinst:
    Code:
    Select 'E-Mail', t1.*
    From e_mail_tab t1
    
    Union All
    
    Select 'Telefon', t2.*
    From telefon_tab t2
    Ansonsten evtl. mal den Tabellenaufbau erklären ;)
     
  6. Heiko

    Heiko Benutzer

    Tabellenaufbau ist so:

    Eine Tabelle beinhaltet die Adresse:
    Felder: Adressnr, Name ...

    die andere Tabelle die Kontaktdaten:
    Felder: Adressnr, Kontaktart, Kontakt...

    In der ersten stehen als:
    4711, Heiko, ...

    dazu in der zweiten:
    4711, Heiko, Email,heiko@irgendwo.de
    4711, Heiko, Telefon1,030-12345
    4711, Heiko, FAX,030-1233445
    ...

    und ich möchte nun folgendes haben:
    4711, Heiko,heiko@irgendwo.de,030-12345,030-1233445
     
  7. akretschmer

    akretschmer Datenbank-Guru


    Tabellen, Werte und Abfrage :

    Code:
    test=*# \d heiko_adresse
     Table "public.heiko_adresse"
     Column |  Type  | Modifiers
    --------+---------+-----------
     nr  | integer | not null  
     name  | text  |
    Indexes:
      "heiko_adresse_pkey" PRIMARY KEY, btree (nr)
    
    test=*# \d heiko_kontakt
     Table "public.heiko_kontakt"
     Column |  Type  | Modifiers
    --------+---------+-----------
     id  | integer |
     typ  | text  |
     wert  | text  |
    
    test=*# select * from heiko_adresse ;
      nr  | name
    ------+-------
     4711 | heiko
    (1 row)
    
    Time: 0,207 ms
    test=*# select * from heiko_kontakt ;
      id  |  typ  |  wert
    ------+-------+-------------------
     4711 | email | heiko@irgendwo.de
     4711 | phone | 0815
     4711 | fax  | 424242
    (3 rows)
    
    Time: 0,093 ms
    test=*# select a.name, array_agg(k.typ || ': ' ||k.wert) from heiko_adresse a left join heiko_kontakt k on a.nr=k.id group by a.name;
     name  |  array_agg
    -------+----------------------------------------------------------
     heiko | {"email: heiko@irgendwo.de","phone: 0815","fax: 424242"}
    (1 row)
    
    Time: 0,402 ms
    test=*# select a.name, array_to_string(array_agg(k.typ || ': ' ||k.wert),', ') from heiko_adresse a left join heiko_kontakt k on a.nr=k.id group by a.name;
     name  |  array_to_string
    -------+----------------------------------------------------
     heiko | email: heiko@irgendwo.de, phone: 0815, fax: 424242
    (1 row)
    
    Time: 0,469 ms
    test=*#
    
    PostgreSQL.
     
  8. Distrilec

    Distrilec Datenbank-Guru

    Funktioniert ab Oracle 11g:
    Code:
    Select adressnr,
      Name,
      (Select listagg(tk.kontakt, ',') within Group(Order By tk.kontaktart)
      From  tabelle_kontaktdaten tk
      Where  tk.kontaktart = 'Email'
      And  tk.adressnr = ta.adressnr
      Group  By tk.adressnr) email,
      (Select listagg(tk.kontakt, ',') within Group(Order By tk.kontaktart)
      From  tabelle_kontaktdaten tk
      Where  tk.kontaktart Like 'Telefon%'
      And  tk.adressnr = ta.adressnr
      Group  By tk.adressnr) telefon,
      (Select listagg(tk.kontakt, ',') within Group(Order By tk.kontaktart)
      From  tabelle_kontaktdaten tk
      Where  tk.kontaktart = 'FAX'
      And  tk.adressnr = ta.adressnr
      Group  By tk.adressnr) fax,
    From  tabelle_adresse ta
    
     
  9. Heiko

    Heiko Benutzer

    Also, danke für die Tipps.
    Dein Vorschlag, akretschmer, funktioniert leider nicht - da ist wohl der SQL Dialekt etwas unterschiedlich von Oracle und Postgres.
    @distrilect: wir benutzen noch eine 8er Oracle DB...
     
  10. Distrilec

    Distrilec Datenbank-Guru

    Schritt 1: DB runterfahren
    Schritt 2: Oracle 11g anschmeißen
    Schritt 3: Problem gelöst ;)

    Spaß beiseite:
    Code:
    Select adressnr,
      Name,
      (Select ltrim(sys_connect_by_path(tk.kontakt, ','), ',')
      From  (Select tk.*,
      rownum rn
      From  tabelle_kontaktdaten tk
      Where  tk.kontaktart = 'Email'
      And  tk.adressnr = ta.adressnr) tk
      Connect By rn = Prior rn + 1
      Start  With rn = 1) email,
      (Select ltrim(sys_connect_by_path(tk.kontakt, ','), ',')
      From  (Select tk.*,
      rownum rn
      From  tabelle_kontaktdaten tk
      Where  tk.kontaktart Like 'Telefon%'
      And  tk.adressnr = ta.adressnr) tk
      Connect By rn = Prior rn + 1
      Start  With rn = 1) telefon,
      (Select ltrim(sys_connect_by_path(tk.kontakt, ','), ',')
      From  (Select tk.*,
      rownum rn
      From  tabelle_kontaktdaten tk
      Where  tk.kontaktart = 'FAX'
      And  tk.adressnr = ta.adressnr) tk
      Connect By rn = Prior rn + 1
      Start  With rn = 1) fax,
    From  tabelle_adresse ta
    
     
  11. akretschmer

    akretschmer Datenbank-Guru

    Tatsache. Das macht keinen Spaß.
     
  12. Heiko

    Heiko Benutzer

    ;) Klar, ich haue mal eben die alte Version wech...

    Heissen Dank Distrilec, ich versuche deinen Tipp mal.

    Was ich aber ganz nebenbei dabei bestätigt bekommen, MS ist mit seiner Db da schon wesentlich entwicklerfreundlicher... Eine simple PIVOT-Anweisung und der Drop wäre gelutscht
     
  13. Distrilec

    Distrilec Datenbank-Guru

    @Heiko Oracle hat da auch so etwas... Nennt sich Model-Clause... Deine Steinzeitversion von Datenbank kann das aber glaub noch nicht ;)
     
  14. ukulele

    ukulele Datenbank-Guru

    Die bisherigen Vorschläge waren ja alle nicht falsch aber eine wichtige Frage vieleicht noch: Hast du 10 Informationstypen und zu jedem Informationstyp immer nur maximal einen Eintrag? Dann kannst du alles mit 10 JOINs machen, das ist überschaubar und so simpel, das jeder es versteht und es auf jeder SQL DB läuft.
     
  15. Heiko

    Heiko Benutzer

    10 Joins?

    Also ich habe in der Tabelle mit den Kontaktdaten das Feld Nummer in dem E-Mail, Telefon etc. gespeichert sind, die KMOART gibt die Kommunikationsart an

    Hiermit bekomme ich die Telefonnummern zur Adressentabelle hinzu:
    SELECT A.Adressenr,K.Komart,K.Nummer
    FROM reladresse A
    left JOIN relkomm K ON a.Adressenr = K.Adressenr WHERE k.komart = 1

    Wie jetzt aber nochmal die RELKOMM joinen?
    Es müsste jetzt ja noch die fehlenden KOMART 2-9 hinzu...?? Irgendwie verbschieden sich gerade bei mit die Hirnzellen rudelweise...
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden