Pivot/Kreuztabellen in Firebird mit SQL erstellen

[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.:

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

' 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:

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?