Ergebnis 1 bis 5 von 5

Thema: Spaltenanzahl einer Abfrage dynamisch Anhand anderer Tabelle erzeugen

  1. #1
    bierstuegbl ist offline Neuer Benutzer
    Registriert seit
    21.11.2006
    Beiträge
    25

    Standard Spaltenanzahl einer Abfrage dynamisch Anhand anderer Tabelle erzeugen

    Hallo zusammen,

    ich wurde heute mit einem Problem konfrontiert, dass mir so bisher eigentlich nicht untergekommen ist :-)
    Ich soll Abfrageergebnisse als csv-Datei liefern, die dann in Excel geladen werden und per Makro in Diagramme umgewandelt werden. Die Makros stehen schon und sollen erstmal nicht verändert werden, das heißt das CSV-Format ist mir genau vorgegeben. Leider erwarten diese Makros Ergebnisstabellen, bei denen die Ergebnisse nicht nur Zeilenweise sondern auch Spaltenweise dynamisch sind.
    Ich bin mir nicht sicher, ob so ein Ansatz mit einer Datenbankanfrage überhaupt möglich ist, mir fällt zumindest im Moment nix Gescheites dazu ein. Zur Klärung hier mal ein Beispiel:

    Nehmen wir an, wir haben eine Tabelle in der für alle Bauteile eines bestimmten Typs (z.B. Typ Stoßstange) Fehler beim Produktionsvorgang abgespeichert werden. Dafür haben wir eine Tabelle, in der alle aufgetretenen Fehler verzeichent sind, und zwar bezüglich eines Bauteils (für jede konkrete Stoßstange, die per id gezeichnet ist) und der Stelle, an der der Fehler auftrat (die Naht). Sie könnte z.B. so aussehen:
    Code:
    bauteil_id   Naht_id   Fehlercode
    1            Naht_1         5
    1            Naht_1         3
    1            Naht_2         5
    1            Naht_2         7
    2            Naht_1         4
    2            Naht_2         6
    2            Naht_2         7
    wobei bauteil_id und Naht_id eben foreign keys sind. An der Stoßstange mit der id 1 traten also die Fehler 5 und 3 an der Naht mit der Naht_id Naht_1 auf, die Fehler 5 und 7 an Naht_2. An der nächsten Stoßstange mit der id 2 trat dann an Naht_1 Fehler 4 auf usw.

    Erwartet wird nun eine Abfrage, die z.B. für jedes Bauteil für alle Nähte des Bauteils (es gibt eine Tabelle in der alle möglichen Fehlerstellen (also Nähte) des Bauteils gelistet sind) angibt, ob dort Fehler aufgetreten sind oder nicht. Nehmen wir an, eine Stoßstange hätte 3 Nähte. Das Ganze sähe dann folgendermaßen aus:

    Code:
    Bauteil_id   Naht_1   Naht_2   Naht_3 usw.
    1            1        1        0
    2            1        1        0
    d.h. pro Bauteil genau eine Zeile und eine dynamische Spaltenanzahl, nämlich pro Naht aus der Nahttabelle eine Spalte (betitelt mit der jeweiligen Naht_id). Als Wert steht in den Nahtspalten 0 falls kein Fehler an dieser Naht auftrat, 1 wenn 1 oder mehr Fehler auftraten, also quasi „Ist die Naht korrekt oder nicht“. Ich habe nun keine Ahnung, wie man dynamisch aufgrund von Informationen in einer anderen Tabelle (der Nahttabelle) die Spaltenanzahl erzeugen und diese dann auch noch mit Werten betiteln soll. Gibt es dafür irgendeinen Weg, den ich einfach nicht sehe? Ich rede hier davon, dass das Ganze durch eine Datenbankabfrage gelöst werden soll, nicht über die Möglichkeit, die Daten per Programm aus der Datenbank zu holen und dann ein Programm die gewünschte CSV-Datei zusammenbasteln zu lassen. Das ist natürlich möglich, wäre aber viel zu starr und ich müsste jeden Select extra behandeln (es gibt noch viel mehr Typen als die Stoßstange und alle haben eigene Nahtdefinitionen etc.).

    Gibt es dafür eine Datenbankseitige Lösungsmöglichkeit? Ich hoffe das Problem ist verständlich geschildert, bei Rückfragen versuch ich natürlich noch mal, das Ganze zu erklären 

  2. #2
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von bierstuegbl Beitrag anzeigen
    Hallo zusammen,

    ich wurde heute mit einem Problem konfrontiert, dass mir so bisher eigentlich nicht untergekommen ist :-)
    Ich soll Abfrageergebnisse als csv-Datei liefern, die dann in Excel geladen werden und per Makro in Diagramme umgewandelt werden. Die Makros stehen schon und sollen erstmal nicht verändert werden, das heißt das CSV-Format ist mir genau vorgegeben. Leider erwarten diese Makros Ergebnisstabellen, bei denen die Ergebnisse nicht nur Zeilenweise sondern auch Spaltenweise dynamisch sind.
    Ich bin mir nicht sicher, ob so ein Ansatz mit einer Datenbankanfrage überhaupt möglich ist, mir fällt zumindest im Moment nix Gescheites dazu ein. Zur Klärung hier mal ein Beispiel:
    Dir reicht also die Ausgabe der CSV-Datei, als Tabelle brauchst Du es nicht wirklich, oder?



    Erwartet wird nun eine Abfrage, die z.B. für jedes Bauteil für alle Nähte des Bauteils (es gibt eine Tabelle in der alle möglichen Fehlerstellen (also Nähte) des Bauteils gelistet sind) angibt, ob dort Fehler aufgetreten sind oder nicht. Nehmen wir an, eine Stoßstange hätte 3 Nähte. Das Ganze sähe dann folgendermaßen aus:

    Code:
    Bauteil_id   Naht_1   Naht_2   Naht_3 usw.
    1            1        1        0
    2            1        1        0

    Gibt es dafür eine Datenbankseitige Lösungsmöglichkeit? Ich hoffe das Problem ist verständlich geschildert, bei Rückfragen versuch ich natürlich noch mal, das Ganze zu erklären 

    Ich denke, das man das durchaus in plpgsql lösen könnte. Du mußt ja nur eine Tabelle erzeugen, die die CSV-Daten enthält, als TEXT, die Du dann ausgeben kannst.
    Den Inhalt, also die CSV-Daten, mit plpgsql zu erstellen sollte nicht das Problem sein, man iteriert halt durch die Tabelle, schaut, welche Spalten bei entstehen, und generiert das CSV.
    Falls ich heute Abend mal lange Weile habe, bastle ich vielleicht mal eine Demo, mal sehen. Hab 'ne schwangere Frau zu Hause... nicht immer Zeit deswegen.

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  3. #3
    bierstuegbl ist offline Neuer Benutzer
    Registriert seit
    21.11.2006
    Beiträge
    25

    Standard

    Hallo Andreas,

    Dir reicht also die Ausgabe der CSV-Datei, als Tabelle brauchst Du es nicht wirklich, oder?
    Jein, theoretisch reicht es mir erstmal als csv, ja. Es muss eben gewährleistet sein, dass die dynamischen Spaltennamen als erste Zeile des csv auftaucht.

    Den Inhalt, also die CSV-Daten, mit plpgsql zu erstellen sollte nicht das Problem sein, man iteriert halt durch die Tabelle, schaut, welche Spalten bei entstehen, und generiert das CSV.
    Hmm, jo, irgendwie würde das wohl schon funktionieren - faktisch ist es dann aber fast das Gleiche, wie wenn ich es mit einer externen Programmiersprache hole, sprich z.B. ein Java oder C# Progrämmchen schreib (ok, auf der Datenbank isses natürlich schneller, aber es ist eben extra eine Funktion geschrieben, um exakt diese eine Anfrage zu bearbeiten, also total starr). Früher oder später wird das wahrscheinlich eh passieren müssen, da ich eine Oberfläche erstellen muss, mit deren Hilfe Auswertungen eben dynamsich zusammengeklickt werden können. Ob ich dann allerdings noch auf Excel ausweiche oder Charts usw. einfach selbst generiere steht noch in den Sternen. Aus meiner Sicht ist es halt recht unflexibel, das auf programmatische Art zu lösen. Im Prinzip schwebt mir sowas vor, dass ich mir unter der oberfläche nen dynamsichen SQl-Genartor bau, der das Ganze dann mit Standard-Sql-Abfragen abhandelt. Vielleicht lieg ich da aber auch falsch und man kann das per PSQL so dynamisch gestalten, dass es wirklich für verschiedene Anfragearten passt. Im Prinzip sollten eigentlich neue Anfragekombinationen möglich sein, ohne dass ich dafür ne neue Funktion schreiben muss.

    Ne Lösungsmöglichkeit in PSQL wär natürlich trotzdem net schlecht - deine schwangere Frau kann ja eh nix unternehmen

  4. #4
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von bierstuegbl Beitrag anzeigen
    Hallo zusammen,

    ich wurde heute mit einem Problem konfrontiert, dass mir so bisher eigentlich nicht untergekommen ist :-)
    Ich soll Abfrageergebnisse als csv-Datei liefern, die dann in Excel geladen werden und per Makro in Diagramme umgewandelt werden. Die Makros stehen schon und sollen erstmal nicht verändert werden, das heißt das CSV-Format ist mir genau vorgegeben. Leider erwarten diese Makros Ergebnisstabellen, bei denen die Ergebnisse nicht nur Zeilenweise sondern auch Spaltenweise dynamisch sind.
    Ich bin mir nicht sicher, ob so ein Ansatz mit einer Datenbankanfrage überhaupt möglich ist, mir fällt zumindest im Moment nix Gescheites dazu ein. Zur Klärung hier mal ein Beispiel:
    Vorweg: vielleicht hab ich Dich zumindest partiell falsch verstanden. Ich hab im folgenden auch auf Details wie CSV verzichtet, dafür aber es so gemacht, daß man Ergebnistabellen hat. Diese kann man ja trivial via COPY als CSV raushauen.

    Nehmen wir an, wir haben eine Tabelle in der für alle Bauteile eines bestimmten Typs (z.B. Typ Stoßstange) Fehler beim Produktionsvorgang abgespeichert werden. Dafür haben wir eine Tabelle, in der alle aufgetretenen Fehler verzeichent sind, und zwar bezüglich eines Bauteils (für jede konkrete Stoßstange, die per id gezeichnet ist) und der Stelle, an der der Fehler auftrat (die Naht). Sie könnte z.B. so aussehen:
    Hier mal meine Tabellen:

    Code:
    test=*# \d bauteile
                             Table "fehler.bauteile"
     Column |  Type   |                       Modifiers
    --------+---------+-------------------------------------------------------
     id     | integer | not null default nextval('bauteile_id_seq'::regclass)
     name   | text    |
    Indexes:
        "bauteile_pkey" PRIMARY KEY, btree (id)
    
    test=*# \d fehlerorte
                             Table "fehler.fehlerorte"
     Column |  Type   |                        Modifiers
    --------+---------+---------------------------------------------------------
     id     | integer | not null default nextval('fehlerorte_id_seq'::regclass)
     ort    | text    |
    Indexes:
        "fehlerorte_pkey" PRIMARY KEY, btree (id)
    
    test=*# \d bauteil_fehlerorte
     Table "fehler.bauteil_fehlerorte"
        Column    |  Type   | Modifiers
    --------------+---------+-----------
     bauteil_id   | integer |
     fehlerort_id | integer |
    Indexes:
        "idx_bauteil_fehlerorte" UNIQUE, btree (bauteil_id, fehlerort_id)
    Foreign-key constraints:
        "bauteil_fehlerorte_bauteil_id_fkey" FOREIGN KEY (bauteil_id) REFERENCES bauteile(id)
        "bauteil_fehlerorte_fehlerort_id_fkey" FOREIGN KEY (fehlerort_id) REFERENCES fehlerorte(id)
    
    test=*# \d fehler
          Table "fehler.fehler"
       Column   |  Type   | Modifiers
    ------------+---------+-----------
     bauteil_id | integer |
     ort_id     | integer |
     fehler     | integer |
    Foreign-key constraints:
        "fehler_bauteil_id_fkey" FOREIGN KEY (bauteil_id, ort_id) REFERENCES bauteil_fehlerorte(bauteil_id, fehlerort_id)
    Die tablen enthalten:

    Code:
    test=*# select * from fehler;
     bauteil_id | ort_id | fehler
    ------------+--------+--------
              1 |      1 |      5
              1 |      1 |      3
              1 |      2 |      5
              1 |      2 |      7
              2 |      1 |      4
              2 |      2 |      6
              2 |      2 |      7
              3 |      2 |      3
    (8 rows)
    
    test=*# select * from bauteile;
     id |   name
    ----+-----------
      1 | Bauteil 1
      2 | Bauteil 2
      3 | Bauteil 3
    (3 rows)
    
    test=*# select * from fehlerorte;
     id |  ort
    ----+--------
      1 | Naht 1
      2 | Naht 2
      3 | Naht 3
    (3 rows)
    
    test=*# select * from bauteil_fehlerorte ;
     bauteil_id | fehlerort_id
    ------------+--------------
              1 |            1
              1 |            2
              1 |            3
              2 |            3
              3 |            2
              3 |            3
              2 |            1
              2 |            2
    (8 rows)
    Erwartet wird nun eine Abfrage, die z.B. für jedes Bauteil für alle Nähte des Bauteils (es gibt eine Tabelle in der alle möglichen Fehlerstellen (also Nähte) des Bauteils gelistet sind) angibt, ob dort Fehler aufgetreten sind oder nicht. Nehmen wir an, eine Stoßstange hätte 3 Nähte. Das Ganze sähe dann folgendermaßen aus:
    Ich hab mal eine Funktion geschrieben, für ein bestimmtes Bauteil die Summe aller Fehler für dieses Bauteil gültigen Fehlerorte ausgibt:
    Code:
    create or replace function fehler(PRODUKT int) returns setof text as $$
    declare
            r1      record;
            r2      record;
            sql     text;
            g       text;
    begin
            sql = 'select ' || $1 || ' as produkt';
            g = '';
            for r1 in select o.id, o.ort from fehlerorte o left join bauteil_fehlerorte b on b.fehlerort_id=o.id where b.bauteil_id = $1 order by 1 l
                    return next r1.ort;
                    sql = sql || ', sum(case when ort_id = ' || r1.id || ' then 1 else 0 end) as "' || r1.ort || '" ';
            end loop;
            sql = sql || 'from fehler where bauteil_id = ' || $1 || ' group by produkt;';
            raise notice '%', sql;
            execute sql;
    end;
    $$ language plpgsql;
    Das ist so noch nicht fertig. Es baut einen String mit einem SQL-Befehl auf, den es als letztes 'raise notice' in voller Länge ausgibt (und dann auch ausführt, aber ohne ein Resultat zu vermelden) Du kannst das in psql probieren und mit C&P mal ausführen. Es erzeugt, aufgerufen mit der ID eines Bauteiles, dynamisch ein SQL, welches eine Table mit den Orten, wo Fehler auftreten können als Spaltennamen zurück.

    Mit Testdaten:

    Code:
    test=*# select * from fehler(2);
    NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1"
    NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2"
    NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3"
    NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3" from fehler where bauteil_id = 2 group by produkt;
     fehler
    --------
     Naht 1
     Naht 2
     Naht 3
    (3 rows)
    
    -- nun via C&P
    
    test=*# create table prod2 as select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3" from fehler where bauteil_id = 2 group by produkt;
    
    
    test=*# select * from prod2 ;
     produkt | Naht 1 | Naht 2 | Naht 3
    ---------+--------+--------+--------
           2 |      1 |      2 |      0
    (1 row)
    Als 'Bonus' siehst Du sogar die Anzahl der Fehler pro Fehlerstelle.

    Die Funktion liefert auf alle Fälle unterschiedliche Tabellen, je nachdem, was definert wurde, und benennt auch die Spalten nach den Fehlerquellen.

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  5. #5
    bierstuegbl ist offline Neuer Benutzer
    Registriert seit
    21.11.2006
    Beiträge
    25

    Standard

    Hallo Andreas,

    kanns heute leider nicht testen, trotzdem schon mal vielen Dank für die Hilfe, echt Klasse :-) Sobald ich dazu kommen, werd ich deine Lösung mal erweitern und schauen, ob das für mich hinhaut.

    Grüße,
    Bierstuebl

Ähnliche Themen

  1. Antworten: 3
    Letzter Beitrag: 23.09.2011, 10:25
  2. Tabelle mit Tabelle aus anderer DB überschreiben
    Von blablub im Forum Allgemeines
    Antworten: 4
    Letzter Beitrag: 02.05.2010, 07:05
  3. Frage Datensätze vor update in anderer Tabelle speichern
    Von eremit im Forum PL/pgSQL
    Antworten: 5
    Letzter Beitrag: 04.02.2010, 11:48
  4. Antworten: 13
    Letzter Beitrag: 15.07.2009, 08:51
  5. Antworten: 1
    Letzter Beitrag: 06.12.2007, 17:08

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum