mehrere Spalten in Ergebnismenge

[Hansa]

Hi,

ich brauche für eine Statistik mehrere Datensätze, die allerdings aus mehreren zusammengefügt werden müssen. Beispiel : zu jedem Artikel existiert pro Monat ein Datensatz. Jetzt sollen die Datensätze 1 bis 12 in einer Zeile dargestellt werden können. Also ungefähr so :

Code:

Artikel 1 Mon1 Mon2 .. Mon12
Artikel 2 Mon1 Mon2 .. Mon12

Die Tabellen sind momentan so aufgebaut, dass jeweils nur die Monate existieren, die <> 0 sind, denn es kann vorkommen, dass nur 1 Monat relevant ist. Statt einzelne Monate könnte man ja auch 12 Monate pro Artikel mitschleppen, aber geht das auch anders ?

[mkinzler]

Nennt man Pivot

[DeddyH]

Oder auch Kreuztabelle.

[Sir Rufo]

Wenn du das Ergebnis als DataSet haben möchtest, dass du einfach an ein DBGrid bindest, dann musst du eine Pivot Abfrage erstellen (und hast pro Artikel auch 12 Monatswerte).

Du kannst aber auch ein StringGrid nehmen und dieses mit den Werten aus der normalen Abfrage füllen.

Kommt jetzt darauf an, was schneller und einfacher umzusetzen ist.

[Hansa]

Nach etwas weitersuchen und zusammenstückeln kriege ich jetzt mit dem hier :

Code:

SELECT (CASE MONAT WHEN 1 THEN UMSATZ ELSE 0 END) AS MON1,
       (CASE MONAT WHEN 2 THEN UMSATZ ELSE 0 END) AS MON2,
       (CASE MONAT WHEN 3 THEN UMSATZ ELSE 0 END) AS MON3,
       (CASE MONAT WHEN 4 THEN UMSATZ ELSE 0 END) AS MON4,
       (CASE MONAT WHEN 5 THEN UMSATZ ELSE 0 END) AS MON5,
       (CASE MONAT WHEN 6 THEN UMSATZ ELSE 0 END) AS MON6,
       (CASE MONAT WHEN 7 THEN UMSATZ ELSE 0 END) AS MON7,
       (CASE MONAT WHEN 8 THEN UMSATZ ELSE 0 END) AS MON8,
       (CASE MONAT WHEN 9 THEN UMSATZ ELSE 0 END) AS MON9,
       (CASE MONAT WHEN 10 THEN UMSATZ ELSE 0 END) AS MON10,
       (CASE MONAT WHEN 11 THEN UMSATZ ELSE 0 END) AS MON11,
       (CASE MONAT WHEN 12 THEN UMSATZ ELSE 0 END) AS MON12
FROM STAT WHERE ID_ART=2874423

das im Anhang. Wichtig ist schon mal, dass die Detail-Datensätze nicht untereinander sondern nebeneinhander angezeigt werden. Nur die Zeilen stimmen noch nicht. 3. Spalte steht auch in 3. Zeile usw. Was fehlt da noch ? Geht das nur mit CASE oder ist das Zufall ?

[mkinzler]

Sollte mit einem ähnlichen View lösbar sein

SQL-Code:

CREATE OR ALTER VIEW UMSATZPROMONAT(
    ARTIKEL,
    JAHR,
    JANUAR,
    FEBRUAR,
    MAERZ,
    APRIL,
    MAI,
    JUNI,
    JULI,
    AUGUST,
    SEPTEMBER,
    OKTOBER,
    NOVEMBER,
    DEZEMBER)
AS
  select
    Artikel as Artikel,
    Jahr as jahr,
    sum( Januar) as Januar,
    sum( Februar) as Februar,
    sum( Maerz) as Maerz,
    sum( April) as April,
    sum( Mai) as mai,
    sum( Juni) as juni,
    sum( Juli) as juli,
    sum( August) as august,
    sum( September) as september,
    sum( Oktober) as oktober,
    sum( November) as november,
    sum( Dezember) as dezember
  from
  (
    select
      Artikel as Artikel,
      Jahr as Jahr,
      iif( monat = 1, umsatz, 0) as Januar,
      iif( monat = 2, umsatz, 0) as Februar,
      iif( monat = 3, umsatz, 0) as Maerz,
      iif( monat = 4, umsatz, 0) as April,
      iif( monat = 5, umsatz, 0) as Mai,
      iif( monat = 6, umsatz, 0) as Juni,
      iif( monat = 7, umsatz, 0) as Juli,
      iif( monat = 8, umsatz, 0) as August,
      iif( monat = 9, umsatz, 0) as September,
      iif( monat = 10, umsatz, 0) as Oktober,
      iif( monat = 11, umsatz, 0) as November,
      iif( monat = 12, umsatz, 0) as Dezember
   from
    (
        select
            Artikel as Artikel,
            extract( year from datum) as jahr,
            extract( month from datum) as monat,
            sum( umsatz) as umsatz
        from
            umsatz
        group by
           1,2,3
            )

    )
    group by Artikel, Jahr
;

[mkinzler]

Zitat:

Zitat von Hansa (Beitrag 1194312)

Nach etwas weitersuchen und zusammenstückeln kriege ich jetzt mit dem hier :

3. Spalte steht auch in 3. Zeile usw. Was fehlt da noch ? Geht das nur mit CASE oder ist das Zufall ?

Es wird pro Monat ein Ergebnis geliefert, du musst noch eine Summe prio Jahr bilden

[Hansa]

Zitat:

Zitat von mkinzler (Beitrag 1194314)

du musst noch eine Summe pro Jahr bilden

Inwiefern Summe ? Ich will ja die Monate einzeln.

P.S.: mit dem View siehts schon richtig aus. Aber warum ein View ?

[Furtbichler]

Weil das hübscher ist. Oder auch:
Weil das eine klare Schnittstelle zwischen Darstellung (EXE) und Erzeugung (DB) schafft.
Du musst die EXE nicht aufbohren, wenn Du die View änderst (z.B. Daten aus einer anderen Tabelle etc.)
und Du kannst die Auswertung auch an anderer Stelle noch gebrauchen (DRY)

btw. geht nicht auch

Code:

select sum(iif(monat = 1, umsatz, 9 )) as Januar,
 ...

[Hansa]

Was nützt es mich, die EXE nicht ändern zu müssen, dafür aber die View bzw. die DB ? :shock: Oder andersrum gefragt : wie kann ich die View parametrisieren ? Geht IMHO nicht. Ich kann ja wohl schlecht am 1.1.2013 eine neue View anlegen, weil es dann auch Daten für 2013 gibt.

[Furtbichler]

Nun ja, es nützt etwas, wenn der Kunde oder wer auch immer die View anpassen kann, ohne die EXE ändern zu müssen, weil aus Sicht der EXE die View ja immer noch die gleichen Daten (von der Struktur her) liefert. Aber wenn Du natürlich mit deiner One Man Show keinen Bedarf an solchen Konzepten hast, dann bringt Dir das natürlich nichts. Ebensowenig scheinst Du Interesse an klaren Strukturen und Paradigmentrennung zu haben, was nicht weiter schlimm ist, da Du ja alleine arbeitest.

Und stell Dir mal vor, Du bietest deinen Kunden irgendwann doch mal einen Reportgenerator an und kannst dann -boah ey- auf eben diese View zurückgreifen, weil, verflixt, die etwas komplexere Query gerade nicht zur Hand ist. Und auch würde dann gelten: Wenn sich die Struktur der Datenbank ändert (bei Dir natürlich nicht, aber bei Amateuren kommt so etwas schon mal vor), dann kann man die Sicht auf die Daten (daher könnte der Name 'View' kommen) ändern, ohne -wie gesagt- die EXE'n, Report-Templates, EXCEL-Dateien, Web-Anwendungen usw. anfassen zu müssen. Das alles hast Du natürlich nicht: Du hast nur diese eine EXE und den Compiler immer dabei.

Aber das sind ja nur hypothetische Überlegungen, die für Profis deines Kalibers eh alle nicht zutreffen.

:stupid:

[Bummi]

Ich schließe mich meinem Vorredner inhaltlich an (ohne den persönlichen Aspekt).
Die Sicht kannst Du möglicherweise nicht ändern (ich weiß nicht ob Deine DB Functiontables kennt), aber Du kannst Sie so formulieren dass sie auch in Jahren noch gültig ist, indem Du das Jahr in die Ausgabe mit aufnimmst und bei der Summierung entsprechend berücksichtigst, wobei eine entsprechende Mitaufnahme von ID_ART in Deinem Fall auch Sinn machen sollte.

[mkinzler]

Zitat:

Inwiefern Summe ? Ich will ja die Monate einzeln.

Hast du ja in den Spalten. Aber du hast 12 DS.

Zitat:

Was nützt es mich, die EXE nicht ändern zu müssen, dafür aber die View bzw. die DB ? Oder andersrum gefragt : wie kann ich die View parametrisieren ?

Dann nimm statt einem View eine selectable SP

Zitat:

Ich kann ja wohl schlecht am 1.1.2013 eine neue View anlegen, weil es dann auch Daten für 2013 gibt.

Der View liefert dir einen DS Pro Jahr unt Artikel. Also entweder datumsabhängiger Zeitraum in den View einbauen und zu SP ( mit Parameter) umbauen.

Zitat:

Zitat von Furtbichler

btw. geht nicht auch

SQL-Code:

select sum(iif(monat = 1, umsatz, 9 )) as Januar,
 ...

?
Warum 9

[dunningkruger]

Zitat:

Zitat von mkinzler (Beitrag 1194284)

Nennt man Pivot

[OT]
Diese "Pivot-Tabellen" (von Excel?) habe ich noch NIE kapiert :oops::evil:

Echt dumm und naiv gefragt:

Ist das, was Hansa in #1 als Code: gepostet hat, tatsächlich ein Beispiel für das Ergebnis einer Pivot-Tabelle?
[/OT]

Danke für's Mitlesen!

[DeddyH]

Hier ist ein ganz netter Artikel zu Pivot (mit T-SQL, aber egal): http://www.itrain.de/knowhow/sql/tsql/pivot/pivot.asp
Nicht vergessen, ganz bis unten zu lesen und den weiterführenden Link anzuklicken ;)

[Furtbichler]

Zitat:

Zitat von mkinzler (Beitrag 1194337)

Zitat:

Zitat von Furtbichler

btw. geht nicht auch

SQL-Code:

select sum(iif(monat = 1, umsatz, 9 )) as Januar,
 ...

?
Warum 9

Weiss ich auch nicht (sollte eine 0 sein). Ich meinte, man könnte das subselect wegkürzen. Hab kein FB zum testen, aber es sollte doch einfacher gehen, oder?

Zitat:

Zitat von Bummi (Beitrag 1194332)

Ich schließe mich meinem Vorredner inhaltlich an (ohne den persönlichen Aspekt).

Sehr schön ausgedrückt das ich mir das hätte sparen können. Hast ja Recht.:oops:

[Hansa]

Zitat:

Zitat von Furtbichler (Beitrag 1194330)

Aber das sind ja nur hypothetische Überlegungen, die für Profis deines Kalibers eh alle nicht zutreffen.

:stupid:

Stimmt, ja. So was kann allerdings nur ein Dummschwätzer sagen, der am grünen Tisch sitzt und sich theoretische Überlegungen Jenseits der Praxis ausdenkt :mrgreen:

Allgemein gültig muss es jedoch sein. Werde den View wohl in SP umbauen. Und @furtbichler: ich könnte auch die betreffende Tabellen umbauen. :stupid:

[mkinzler]

Zitat:

Weiss ich auch nicht (sollte eine 0 sein). Ich meinte, man könnte das subselect wegkürzen. Hab kein FB zum testen, aber es sollte doch einfacher gehen, oder?

Er hat die Aggegatsfunktion hier nicht wollen, deshalb habe ich es mit der Derived Table implementiert.

[Furtbichler]

Moin,
das lese ich zwar nicht heraus, aber offensichtlich würde es auch einfacher gehen (dein Beispiel ist dafür aber sehr verständlich).

[Hansa]

Zitat:

Zitat von Furtbichler (Beitrag 1194330)

Aber das sind ja nur hypothetische Überlegungen, die für Profis deines Kalibers eh alle nicht zutreffen.

Haha, Nach der Überheblichkeit kommt meist der tiefe Fall oder zumindest die Erleuchtung. :shock: :mrgreen: Siehe hier :

Zitat:

Zitat von Furtbichler (Beitrag 1194345)

Weiss ich auch nicht (sollte eine 0 sein). Ich meinte, man könnte das subselect wegkürzen. Hab kein FB zum testen, aber es sollte doch einfacher gehen, oder?

Wenn ich das so im nachhinein lese :

Es geht mit einem Wort mehr in meinem SQl in Beitrag #5. Das wars. Kein Subselect, kein View, kein IIF (obwohl bei mir das CASE wohl die selbe Wirkung hat). Jedenfalls reichen 15 Zeilen als normales SQL Statement grundsätzlich aus. Habe da dann noch ein paar Parameter eingebaut und die where-Bedingung verfeinert und fertig. Ist auch bereits in Delphi teilweise eingebaut bzw. umgesetzt.

Ausdrücklicher Dank geht an mkinzler und DeddyH. :thumb:

[Furtbichler]

Zitat:

Zitat von Hansa (Beitrag 1194610)

[
Haha, Nach der Überheblichkeit kommt meist der tiefe Fall oder zumindest die Erleuchtung.

Schon bemerkt? Wenn ich tief falle, musst Du hoch fliegen, damit wir uns in der Mitte treffen.

Soviel zum Niveauunterschied.
:mrgreen:

Und damit es nicht bierernst wird: :cheer:

[Hansa]

Ich habe jetzt hier folgendes im SELECT einer SP stehen:

Code:

    CASE JAHR
      WHEN :VERGLJAHR THEN JAHR
      ELSE 0
    END AS VERGLJAHROUT,
    CASE JAHR WHEN :VERGLJAHR THEN
    SUM (CASE MONAT WHEN 1 THEN UMSATZ
         ELSE 0
         END)
    ELSE 0
    END AS MON1,
    CASE JAHR WHEN :VERGLJAHR THEN
    SUM (CASE MONAT WHEN 2 THEN  UMSATZ
         ELSE 0
         END)
    ELSE 0
    END AS MON2

Das Ganze soll jetzt für 2 Jahre und dann insgesamt 24 Monate funktionieren. Es geht zwar jetzt so auch, aber an allen Ecken frage ich ja den Parameter für das jewilige Jahr ab. Kann man das kürzer hinkriegen ? Also ungefähr so :

Code:

    CASE JAHR
      WHEN :VERGLJAHR THEN /*  hier 12 Monate von VerglJahr behandeln
      WHEN :AUSGJAHR THEN /*   12 Monate von AusgJahr... 
      ELSE 0
    END AS ??

[Hansa]

Das ist jetzt alles soweit erledigt. Allerdings noch eine Frage :

Wie werden die angezeigten Felder gruppiert ? Mit dem hier :

Code:

returns (
    artnr integer,
    mon01 decimal(15,2),
    mon02 decimal(15,2),
    mon03 decimal(15,2),
...
    altmon01 decimal(15,2),
    altmon02 decimal(15,2),
    altmon03 decimal(15,2),
...
INTO :ARTNR,
  :MON01,:MON02,:MON03,...ALTMON01,:ALTMON02,:ALTMON03...

wird mir zumindest in IBEXPERT folgendes angezeigt :

von links nach rechts : altmon1,altmon2,mon01,mon02,altmon3, usw.

[mkinzler]

Wie willst du Gruppieren?

[Hansa]

Ich will das zusammenhängend auf einen Blick sehen. MON1..MON12 und dann ALTMON1..ALTMON12,

Momentan sieht so aus : siehe Anhang. Also gemischt. Warum ?

[mkinzler]

Wie sieht der Select hierzu aus?

[Hansa]

Code:

  FOR SELECT
    A.NR,
    SUM (case MONAT||JAHR when 1||:AUSGJAHRVAR then UMSATZ else 0 END) AUSGJAHR_M1,
    SUM (case MONAT||JAHR when 2||:AUSGJAHRVAR then UMSATZ else 0 END) AUSGJAHR_M2,
    SUM (case MONAT||JAHR when 3||:AUSGJAHRVAR then UMSATZ else 0 
...
    SUM (case MONAT||JAHR when 1||:VERGLJAHRVAR then UMSATZ else 0 END) VERGLJAHR_M1,
    SUM (case MONAT||JAHR when 2||:VERGLJAHRVAR then UMSATZ else 0 END) VERGLJAHR_M2,

[mkinzler]

Ich meinte die Abfrage der SP nicht deren Code. Hier kannst du ja die Reihenfolge der Felder vorgeben.

[Hansa]

Welche Abfrage ? Die aus dem Programm ? Wir sind jetzt lediglich bei IBExpert angekommen.

[sx2008]

Also ich würde das Problem mit einem Memory-Dataset lösen.
Schritt 1:
man benötigt eine Abfrage gruppiert und sortiert nach ArtikelNr, Jahr und Monat:

Code:

ArtikelNr  | Jahr  | Monat | Summe   | MaxWert | Anzahl 
=============================================================
 100      | 2011 | 1     | 6785.70 | 559.00  | 44
 100      | 2011 | 3     | 2782.50 | 120.0   | 17
....

Zusätzlich zur Summe des Preises habe ich noch den Max. Artikelbetrag und die Anzahl aufgenommen.
Die weiteren Felder bekommt man quasi zum Nulltarif und kann damit später interessante statische Daten anzeigen.

Schritt 2:
Man erzeugt dynamisch zur Laufzeit die Felder für das Memory-Dataset.
Das Memory-Dataset stellt dann die Pivot-Tabelle dar.
Dazu muss man in einer Schleife über alle Datensätze der obigen Abfrage laufen und ein neues Feld vom Typ Currency erzeugen.
Man erzeugt z.B. beim Datensatz für Jahr=2011 und Monat=3 das Feld "J2011M03" oder man macht es so wie man es eben benötigt.
Es muss aber geprüft werden, ob das Feld schon angelegt ist.
Im Event AfterOpen des Memory-Datasets kann man den Feldern über das Property DisplayLabel lesbare Bezeichnungen geben.

Schritt 3:
In einer weiteren Schleife (2. Durchlauf) über alle Datensätze wird das Memory-Dataset befüllt.
Dabei muss man prüfen, ob schon ein Datensatz zum Artikel existiert.
Falls nein mit Append einen neuen Dataset anlegen; falls ja Datensatz mit Edit verändern.
Man hat jetzt die Möglichkeit die Summe, MaxWert, Anzahl oder Durchschnitt in das Memory-Dataset zu schreiben.

Vorteile:
* maximale Flexibilität (kein Problem die Daten quartalsweise oder je Kalenderwoche zu präsentieren)
* Unabhängigkeit von der Datenbank
* wesentlich einfachere SQL-Abfrage (schneller und einfacher zu verstehen)
* SQL-Abfrage kann leichter mit einer einschränkenden WHERE-Bedingung erweitert werden
* Grundprinzip ist leicht auf andere Tabellen (Bestellungen, Rückläufer,...) umsetzbar
Nachteil:
* man benötigt ein Memory-Dataset und geschätzte 50 bis 100 Zeilen Sourcecode zur Umsetzung

Man könnte eine Klasse schreiben, die die Schritte 2 und 3 konfigurierbar und allgemeingültig umsetzt.

[Hansa]

Ich war schon kurz davor, das mit einem Clientdataset (CDS) zu realisieren bzw. war das schon so weit fertig. Das war aber nur so, weil es mir anfangs nicht gelang, mehr als ein Jahr in die Ergebnismenge reinzukriegen (also 24 Felder in eine Zeile). Auch war das problematisch, wenn bei einem Artikel ein ganzes Jahr fehlt. Und so etwas

Code:

SUM (CASE JAHR WHEN 2012 THEN CASE (Monat WHEN 1...)

also geschachtelt, das geht nun mal nicht. Zumindest nicht mit Firebird. Daher ja auch mein Kunstgriff mit dem 1||:AUSGJAHR etc. Wenn so was geschachtelt mit 2 Feldern nicht geht, dann werden eben aus 2 Feldern eines gemacht und fertig. :shock: Das war der Trick. Allerdings hat der auch das ClientDataSet an dieser Stelle überflüssig gemacht.

Jetzt wird zuerst mal alles absolut wichtige aus der DB gelesen, das heisst die entsprechenden Artikelnummern und eben 24 Umsatzzahlen. Eventuell wird damit allerdings dann doch wieder ein CDS bestückt. Da muss man mal noch sehen. Jedenfalls ist jetzt eines sichergestellt : zu jedem Artikel gehört (und es ist auch sichergestellt, dass nichts fehlt) eine Reihe von Zusatzdaten, also in dem Fall 24 Zahlen. Auch ohne CDS ist das bereits jetzt sehr flexibel.

Die Grunddaten sind jedenfalls nun vorteilhaft aufbereitet verfügbar und es stellt sich nur noch die Frage, ob mehr Logik bzw. Daten in die stored Procedure verlagert wird, oder lieber ins eigene Programm (z.B. Sortierung, Berechnungen etc.).

Vielleicht ist allerdings noch das angesprochene Memory-Dataset für den einen oder anderen nicht so geläufig. Der Begriff ist zwar so verständlicher, aber dabei handelt es sich in Delphi um ein ClientDataSet (das kann allerdings noch mehr). D.h., man definiert sich mit ein paar Zeilen im eigenen Programm eine eigene Tabelle, die es in keiner DB gibt, behandelt die aber so, als ob das ein "richtiges" Dataset sei. D.h. zum Bsp., dass man diese Quasi-DB-Tabelle in einem DBGrid oder in Reports verwenden kann. Ist auf jeden Fall leichter zu behandeln als richtige DB und kennt mehr Datentypen. Ich empfehle mal jedem, sich das näher anzuschauen, der das so noch nicht kennt. Denn : wer hier in der DP oder in anderen Foren mal nach ClientDataset sucht, der wird nicht soviel finden. Das könnte den Eindruck erwecken, dann wäre das auch nicht so wichtig, aber das ist nicht so ! Die letzte Zeit war ich auf ziemlich vielen Konferenzen u.ä. Ekon, Firebird-Konferenz, Delphi-Tage... Da lief mir überall ein ClientDataset über den Weg. :P Ich war da schon etwas verwundert.