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.