Pivot/Kreuztabellen in Firebird mit SQL erstellen

[Chemiker]

Hallo,

mit folgenden SQL-String werden die Daten in einem DBGrid angezeigt.

Delphi-Quellcode:

SQLString:= 'SELECT *FROM '+TabellenName+' where (MW_MSID LIKE :aMSId) and'+
              ' (MW_DatumUhrZeit between :aStartDatum and :aEndeDatum)';

Ausgabe:

MW_ID----MW_MSID---MW_DATUMUHRZEIT---------MW_MW
2609327--K01T1-------05.03.2008 00:00------------55,2
2609341--K01D1-------05.03.2008 00:00------------5046
2609355--K01T1-------05.03.2008 00:01------------162,7
2609369--K01D1-------05.03.2008 00:01------------5036,1

Die Ausgabe sollte aber so sein:

MW_DATUMUHRZEIT---K01T1--K01D1
05.03.2008 00:00------55,2----5046
05.03.2008 00:01------162,7---5036,1


Wie kann man mit Hilfe von SQL, die Ausgabe in einem DBGrid verändern?

Bis bald Chemiker

[edit=mkinzler]Threadtitel auf Wunsch des Te geändert Mfg, mkinzler[/edit]

[DeddyH]

Statt des * alle interessanten Spalten in der gewünschten Reihenfolge angeben.

[Chemiker]

Hallo DeddyH,

das ist schon klar, aber so wird die Reihenfolge der Spalten nur verändert, aber die Anzeige bleibt.

Bis bald Chemiker

[mkinzler]

Dann hast du die Reihenfolge im Grid so festgelegt ( Columns) und musst die Reihenfolge deshalb dort ändern

[Chemiker]

Hallo mkinzler,

unter „Ausgabe:“ stehen in der 1 Reihe die Datenbankfelder und danach kommen die Werte.

Unter „Die Ausgabe sollte aber so sein:“ soll das Datum/Uhrzeit nur einmal stehen und unter den Datenbankwerten „K01T1“ und „K01D1“ „usw.“ sollen die Werte stehen die unter MW_MW normalerweise stehen.
Kurz gesagt es sollen Datenbank-Werte als Spaltenköpfe angezeigt werden.

Bis bald Chemiker

[mkinzler]

Die Spaltennamen kann man auch bei den Columns einstellen. Mir ist aber nicht ganz klar, wie die Ausgabe aussehen soll bzw. ob ein Grid überhaupt die richtge Anzeigekomponente ist. Könntest du das gewünschte Aussehen etwas geanuer skizzieren?

[alzaimar]

Ah, Du möchtest eine Pivot-Darstellung deiner Daten.
So ungefähr könnte es klappen:

SQL-Code:

select MW_DATUMUHRZEIT,
       SUM (IIF (MW_MSID = 'K01T1', MW_MW,0)) as K01T1,
       SUM (IIF (MW_MSID = 'K01D1', MW_MW,0)) as K01D1
From Tabelle
where (MW_MSID LIKE :aMSId) 
  and (MW_DatumUhrZeit between :aStartDatum and :aEndeDatum)
group by MW_DATUMUHRZEIT

[Chemiker]

Hallo alzaimar,

danke für den Hinweis.
Mit IBExpert hat es relativ schnell geklappt, aber als SQL-String habe ich doch rechte lange für die Umsetzung gebraucht.

So sieht die 1 Version jetzt aus, als String:

Delphi-Quellcode:

SQLString:= 'SELECT MW_DATUMUHRZEIT,'+
       ' SUM (IIF (MW_MSID = ' +'''K01T1''' +', MW_MW,0)) AS K01T1,'+ 
       ' SUM (IIF (MW_MSID = ' +'''K01D1''' +', MW_MW,0)) AS K01D1'+
       ' FROM mw_tabelle'+ 
       ' where (MW_MSID LIKE '+'''K01%'''+')'+ 
       ' and (MW_DATUMUHRZEIT between '+'''05.03.2008'''+' and '+'''06.03.2008'''+')'+
       'GROUP BY MW_DATUMUHRZEIT';

als große Fehlerquelle hat sich die richtige Anzahl an ’ und Leerzeichen an der richtigen Position herausgestellt, die Fehlermeldungen die dabei produziert werden, sind dabei wenig aussagekräftig.

So sieht das im IBExpert-SQL-Editor aus:

SQL-Code:

select MW_DATUMUHRZEIT, 
       SUM (IIF (MW_MSID = 'K01T1', MW_MW,0)) as K01T1, 
       SUM (IIF (MW_MSID = 'K01D1', MW_MW,0)) as K01D1
From mw_tabelle
where (MW_MSID LIKE 'K01%')
  and (MW_DatumUhrZeit between '05.03.2008' and '06.03.2008')
group by MW_DATUMUHRZEIT

Es soll nun noch dynamischer werden, dass wird aber morgen in Angriff genommen.

Bis bald Chemiker

[nahpets]

Hallo,

für meinen Editor habe ich mir mal dieses Script mit Pascalscript geschrieben.

Delphi-Quellcode:

program Sql2Source;
Var
        i : Integer;
begin
  Output.Clear;
  Output.Add('with qry.sql do begin');
  Output.Add('  clear;');
  for i := 0 to Editor.Count - 1 do begin
    Output.Add('  Add(''' + AnsiReplaceText(Editor[i],'''','''''') + ''');');
  end;
  Output.Add('end;');
  Log.Clear;
  Log.Add('SQLString := '''' ');
  for i := 0 to Editor.Count - 1 do begin
    Log.Add(' + ''' + AnsiReplaceText(Editor[i],'''','''''') + ' ''');
  end;
  Log[Log.Count - 1] := Log[Log.Count - 1] + ';';
end.

Da kommt dann dieses

Delphi-Quellcode:

with qry.sql do begin
  clear;
  Add('select MW_DATUMUHRZEIT,');
  Add('       SUM (IIF (MW_MSID = ''K01T1'', MW_MW,0)) as K01T1,');
  Add('       SUM (IIF (MW_MSID = ''K01D1'', MW_MW,0)) as K01D1');
  Add('From mw_tabelle');
  Add('where (MW_MSID LIKE ''K01%'')');
  Add('  and (MW_DatumUhrZeit between ''05.03.2008'' and ''06.03.2008'')');
  Add('group by MW_DATUMUHRZEIT');
end;

und jenes

Delphi-Quellcode:

SQLString := ''
 + 'select MW_DATUMUHRZEIT, '
 + '       SUM (IIF (MW_MSID = ''K01T1'', MW_MW,0)) as K01T1, '
 + '       SUM (IIF (MW_MSID = ''K01D1'', MW_MW,0)) as K01D1 '
 + 'From mw_tabelle '
 + 'where (MW_MSID LIKE ''K01%'') '
 + '  and (MW_DatumUhrZeit between ''05.03.2008'' and ''06.03.2008'') '
 + 'group by MW_DATUMUHRZEIT ';

heraus und das leidige '-Thema ist erledigt. Eventuell kannst Du ja damit was anfangen und Dir ein kleines Delphiprogrämmelchen schreiben, das aus einem SQL den passenden Quelltext bastelt. Sollte mit wenig Aufwand zu realisieren sein und hilft Stunden bei der Fehlersuche zu sparen. Meine SQL's werden in der entsprechenden Datenbankoberfläche erstellt und getestet und dann durch das Script gejagt und fertig ist.

Editor ist das Eingabefeld (TSynEdit) des Editors, Output und Log sind Ausgabefelder (ebenfalls TSynEdit), die als TStrings ans Script übergeben werden. Mit drei TMemos (und 'nem Dutzend zusätzlicher Quelltextzeilen) sollte das in ein paar Minuten zu realisieren sein.

[Chemiker]

Hallo nahpets,

keine schlechte Idee.

Die TSynEdit’s kann man durch TMemo’s ersetzen?

Bis bald Chemiker

[nahpets]

ja

[Chemiker]

Hallo,

noch eine weitere Frage wie kann ich den Spalten-Name variabel machen, irgendwie komme ich da nicht so richtig weiter.

Delphi-Quellcode:

/////////////////////////  Version 2 //////////////////////////////////////////
 SQLString:= 'SELECT MW_DATUMUHRZEIT,'+
       ' SUM (IIF (MW_MSID = :aMS, MW_MW,0)) AS K01T1,'+ 
       ' SUM (IIF (MW_MSID = ' +'''K01D1''' +', MW_MW,0)) AS K01D1'+
       ' FROM '+TabellenName+ 
       ' WHERE (MW_MSID LIKE :aMSId)'+ 
       ' AND (MW_DATUMUHRZEIT BETWEEN :aStartDatum and :aEndeDatum)'+
       'GROUP BY MW_DATUMUHRZEIT';
///////////////////////////////////////////////////////////////////////////////

Es sollte K01T1 durch eine Variable ersetzt werden:

z.B.:

Delphi-Quellcode:

' SUM (IIF (MW_MSID = :aMS, MW_MW,0)) AS K01T1,'+

durch

Delphi-Quellcode:

' SUM (IIF (MW_MSID = :aMS, MW_MW,0)) AS :Variable,'+

das Funktioniert aber leider nicht. Wie kann man das Ändern?

Bis bald Chemiker

[Chemiker]

Hallo,

ich habe jetzt eine PROCEDURE in IBExpert erstellt um eine Kreuztabelle auszugeben. Das Problem dabei ist, dass nur 1 Spalte ausgegeben wird.

SQL-Code:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE PIVOTTABELLE (
    ma_id2 varchar(5) character set win1252,
    start1 timestamp,
    ende1 timestamp,
    maschiene varchar(5) character set win1252)
returns (
    spalte1 double precision,
    mw_datumuhrzeit timestamp)
as
declare variable loVariable varChar(5);
BEGIN
  FOR
    /* Hier mit werden die Messstellen ermittelt */
    select distinct mw_msid from mw_tabelle into :loVariable 
    do

    for
    select mw_datumuhrzeit,sum (iif(mw_msid = :loVariable, MW_MW, 0))
    From mw_tabelle
    where (mw_msid = :loVariable)
      and (MW_DatumUhrZeit between :start1 and :ende1)
    group by mw_datumuhrzeit
    INTO :MW_DATUMUHRZEIT,
         :spalte1 do
    SUSPEND;

 begin
   SUSPEND;
 end
 end^

SET TERM ; ^

GRANT SELECT ON MW_TABELLE TO PROCEDURE PIVOTTABELLE;

GRANT EXECUTE ON PROCEDURE PIVOTTABELLE TO SYSDBA;

Es wird zuerst die Messstellen ermittelt und anschließend als Spalte angezeigt.

Gibt es eigentlich irgendwo Beispiel-Code, wie man so etwas umsetzen kann?

Bis bald Chemiker

[Hansa]

Gucke mal in dem fibplus dataset

[Chemiker]

Hallo hansa,

in der Unit? Wie soll mir das weiterhelfen?

Kennst Du vielleicht Doko. wie man mit Firebird eine Kreuztabelle (Pivot) – Tabelle erstellt mit dynamischen Spalten?

In Access gibt’s extra ein Befehl (Transform) dafür, das fehlt aber bei Firebird.

Bis bald Chemiker

[alzaimar]

Es sollte doch möglich sein, eine kleine Delphi-routine zu schreiben, die ein entsprechend gearteten SQL-SELECT-Befehl nach Durchsicht der Tabelle zu erstellen. Das ist zwar etwas umständlicher, aber wenn es unbedingt ohne Fremdkomponenten gehen soll, muss man eben zu solchen Mitteln greifen.

Eine Alternative wäre ein In-Memory-Dataset (TClientDataset z.B.), dessen Field-Kollektion (die Spalten) Du anhand der Pivot-Felder und deren Inhalte erstellst. Aus eigener Erfahrung kann ich Dir sagen, das sich die Anschaffung einer Fremdkomponente eher lohnt, denn der Aufwand, s oetwas ordendlich hinzubekommen ist doch nicht Ohne. Ich spreche hier z.B. von mehreren Pivot-Feldern, Gruppierungen, Zeilensummen usw. usw.

Falls es Dir reicht, nur das eine Feld als Pivot-Element zu verwenden, sollte das jedoch ohne großen Aufwand möglich sein.

[Chemiker]

Hallo alzaimar,

eine Komponente scheidet aus. Das Programm soll nur diese Daten nach Excel übertragen, aus Geschwindigkeitsgründen soll das ganze auf den Server aufbereitet werden.

Das Problem ist ja nicht so speziell, aber es scheint so, als ob es in Firebird noch nicht umgesetzt worden ist, jedenfalls habe ich bis jetzt noch nichts Passendes gefunden.

Aber trotzdem Danke an alle.

Bis bald Chemiker

[alzaimar]

Ich habe es mit einem In-Memory-Dataset gelöst. War nicht so schwer.

Ich könnte den Code mal rauskramen, aber dazu müsste ich aufstehen.

Puh. Und ich sitz hier grad so schön...

Na gut...

*Ächz*..*Watschel*..*Plumps* (rollback)

So.

Delphi-Quellcode:

unit PivotCreator;

interface

uses
  DB, DBClient;
(*
 * Erstellt eine simple Pivot-Tabelle aDest aus den Daten von aSrc.
 * aPivotKey ist der Name des Schlüsselfeldes
 * aPivotElement der Name des Feldes, dessen Inhalte als Spaltenbezeichnung herhalten sollen. Ich stelle den Inhalten ein 'V' voran.
 * aPivotValue ist der Name des Feldes, dessen Wert in der Spalte des Pivot-Elementes addiert werden soll
 *
 * Beispiel
 * Name   | Größe | Menge | Schuh
 * Meier  |  42   |  1    | Budapester
 * Schulz |  39   |  1    | Sneaker
 * Fuh    |  42   |  1    | Sandale
 * Man    |  38   |  2    | Sneaker
 * Shuh   |  39   |  1    | Sandale
 *
 * ergibt mit (PivotKey = 'Schuh', PivotElement = 'Größe' und PivotValue='Menge'
 * diese Tabelle
 *
 * Schuh      | V38 | V39 | V42 | Total
 * Budapester |     |     |  1  |  1
 * Sandale    |     |  1  |  1  |  2
 * Sneaker    |  2  |  1  |     |  3
 *)
procedure CreatePivot(aSrc: TDataSet; aPivotKey, aPivotElement, aPivotValue: string; aDest: TClientDataset);
implementation
uses Classes, SysUtils, Variants;

procedure CreatePivot(aSrc: TDataSet; aPivotKey, aPivotElement, aPivotValue: string; aDest: TClientDataset);
var
  sValueGroups: TStringlist;
  sVgName: string;
  fCode, fValue, fAmount: TField;
  I, n: Integer;

  procedure _AddField(aFieldName: string;
    aDataType: TFieldType = ftInteger; aSize: Integer = 0);
  begin
    with DefaultFieldClasses[aDataType].Create(aDest) do begin
      FieldName := aFieldName;
      Calculated := False;
      Size := aSize;
      FieldKind := fkData;
      DataSet := aDest;
    end;
  end;

  function VgName(aNum: string): string;
  begin
    if aNum = '' then anum := '(None)';
    Result := Format('V%s', [aNum]);
  end;

  function VariantAdd(a, b: Variant): Variant;
  begin
    if VarIsNull(a) then
      Result := b
    else
      if VarIsNull(b) then
        Result := a
      else
        Result := a + b;
  end;

begin
  aDest.DisableControls;
  sValueGroups := TStringlist.Create;
  sValueGroups.Sorted := True;
  sValueGroups.Duplicates := dupIgnore;
  try
    aSrc.active := True;
    fValue := aSrc.FieldByName(aPivotElement); // Pivot Element
    with aDest, Fields do
      while Count > 1 do
        Remove(Fields[1]);

    with aSrc.FieldByName(aPivotKey) do
      _AddField(aPivotKey, DataType, Size);
    aSrc.First;
    while not aSrc.Eof do begin
      sValueGroups.Add(VgName(fValue.AsString));
      aSrc.next;
    end;
    aDest.IndexFieldNames := aPivotKey;
    for i := 0 to sValueGroups.Count - 1 do
      _AddField(sValueGroups[i]);

    _AddField('Total');
    aDest.CreateDataSet;
    aDest.Open;

    aSrc.First;
    fCode := aSrc.FieldByName(aPivotKey);
    fAmount := aSrc.FieldByName(aPivotValue);
    while not aSrc.Eof do begin
      if not aDest.Locate(aPivotKey, fCode.AsString, []) then begin
        aDest.append;
        aDest[aPivotKey] := aSrc[aPivotKey];
      end
      else
        aDest.edit;
      sVgName := VgName(fValue.AsString);
      aDest[svgName] := VariantAdd(aDest[sVgName], fAmount.AsVariant);
      aDest['Total'] := VariantAdd(aDest['Total'], fAmount.AsVariant);
      aDest.Post;
      aSrc.next;
    end;
    aSrc.Close;
  finally
    sValueGroups.Free;
    aDest.EnableControls;
  end;
end;

end.

Superschnell isses nicht, und man kann es bestimmt ausbauen. Mir hats gereicht.

[Chemiker]

Hallo,

ich habe jetzt eine Kreuztabelle erstellt.

Jetzt habe ich noch ein Geschwindigkeitsproblem mit folgenden Anweisungen.
Vermutlich liegst an der SQL – Anweisung:

SQL-Code:

SELECT DISTINCT mw_msid FROM mw_tabelle

Gibt es eine Möglichkeit diese zu Beschleunigen?

Delphi-Quellcode:

procedure TForm3.btSQLTextvorbelegenClick(Sender: TObject);
begin
  Memo1.Lines.Text:= 'SELECT DISTINCT mw_msid FROM mw_tabelle';    
end;

procedure TForm3.Button1Click(Sender: TObject);
var
  MAListe: TStringList;
  I: integer;
begin
  Memo3.Clear;
  MAListe:= TStringList.Create;
  try
    with SQLFP.pFIBDataSet do
    begin
      Close;
      SelectSQL.Text:= Memo1.Text;
      GenerateSQLs;
      Open; 
      while not EOF do
      begin
        // Die MSId-Bezeichnung aus der Datenbank wird in einer StringList
        // gespeichert. Diese Bezeichung ist auch gleichzeitig die Spalten-
        // bezeichnung für die PIVOT-Tabelle.
        MAListe.add(FieldByName('mw_msid').AsString);
        next;       
      end;
    end;

Bis bald Chemiker

[mkinzler]

Wie sieht die Struktur dieser Tabelle aus?

[mschaefer]

Moin, moin,

Was mir zur Überschrift noch einfällt ist, dass man in den seltensten Fällen die Feldnamen in den Spalten des Grids haben möchte. Eigentlich braucht die Query-Komponente schon eine Übersetzungsliste für die Feldcaptions, da man derzeit bei jedem Grid die Captions eingeben muß.

// Grüße - Martin


PS: Da die Überschrift inzwischen geändert wurde trifft der Beitrag natürlich nicht mehr so zielsicher...live is change...

[Hansa]

Zitat:

Zitat von mschaefer

..Eigentlich braucht die Query-Komponente schon eine Übersetzungsliste für die Feldcaptions, da man derzeit bei jedem Grid die Captions eingeben muß.

Ne, ist bei FIBPlus schon geregelt. Z.B. für mehrsprachige Anwendungen. Aber nicht wie in #14 angesprochen im FIBDataset, sondern in FIBDatabase kann man das einstellen. "UseRepositories" Glaube es gibt drei Arten, z.B. für Fehlermeldungen, Feld-Formate usw. Aber Achtung ! Sofern da was auf true gestellt wird, dann werden noch ein paar Zusatztabellen in der DB angelegt. In irgendeinem DevGuide ist das ausführlich beschrieben.

[mschaefer]

Hm ja - hm - ok,das geht punktemäßig an Dich und Deine FIB-Truppe...
Da müssen die Zeos-Leute und ich jetzt mal drüber Fachsimpeln...

-- Der ist aber auch hartnäckig mit dem FIB - :wink:

Viele Grüße // Martin

[Hansa]

Zitat:

Zitat von mschaefer

.
Da müssen die Zeos-Leute und ich jetzt mal drüber Fachsimpe. -- Der ist aber auch hartnäckig mit dem FIB - :wink:

Viele Grüße // Martin

Soll ich dem fragesteller über dinge berichten, die er schon hat und eventuell nur nicht gesehen hat oder über zeugs, was in zeos sowieso nicht da ist ? :mrgreen:

[Chemiker]

Hallo,

@mkinzler:
TabellenStrucktur: siehe Anhang

@mschaefer:
Die Feldnamen können in diesem Fall, ohne Probleme übernommen werden. Das DBGrid dient mir im Augenblick nur zur Kontrolle, später sollen die Daten in dieser Form nach Excel übertragen werden.

@Hansa:
Ja, es werden dadurch 3 neue Tabellen angelegt: FIB$FIELDS_INFO usw., dass ist nur leider nicht mein Problem gewesen, sondern die Erstellung einer PIVOT/Kreuztabelle aus einer Firebird-Tabelle. Darüber habe ich leider überhaupt nichts gefunden.

Zitat:

Zitat von Hansa

Soll ich dem fragesteller über dinge berichten, die er schon hat und eventuell nur nicht gesehen hat oder über zeugs, was in zeos sowieso nicht da ist ?

Ich höre!

Erst durch alzaimar und nahpets bin ich überhaupt weiter damit gekommen.

Was jetzt noch nicht zufrierendstellend ist, ist die Geschwindigkeit wie die einzelnen Messstellen aus der MW_Tabelle herausgesucht werden. Die Abfrage danach ist kein Problem.

Bis bald Chemiker