ColumnStr := TStringList.Create;
ParallColumnStr := TStringList.Create;
ColumnStr.Add('FSize'); //把Size進行旋轉，其對應的值為Qty
ParallColumnStr.Add('FQty');
dtWhirlData := WhirlDataSet(dtQuery, ColumnStr, ParallColumnStr, 0); //最後一下參數表示以前幾列作為分組
Function WhirlDataSet(DtSet: TAdoDataSet; ColumnToRow, ParallelismRowValue: TStrings; iFrontGrp: Integer): TAdoDataSet;
var
i: Integer;
DtGenTmp: TAdoDataSet;
OldFixFlds: TStrings;
NwFldsName: TStrings;
NwFldsType: TStrings;
NwFldsLen: TStrings;
FieldType: TFieldType;
iColumn, iRow: integer;
LastValue: TStrings;
WillNewGrp: Boolean;
iNewFrontGrp: Integer;
begin
//本函數未對ColumnToRow/ParallelismRowValue的字段是否存在於DtSet作判斷，所以在調用本函數前用戶自己處理
if ColumnToRow.Count <> ParallelismRowValue.Count then
begin
if ColumnToRow.Count > ParallelismRowValue.Count then
begin
for i := ColumnToRow.Count - 1 downto ParallelismRowValue.Count - 1 do
begin
ColumnToRow.Delete(i);
end;
end else
begin
for i := ParallelismRowValue.Count - 1 downto ColumnToRow.Count - 1 do
begin
ParallelismRowValue.Delete(i);
end;
end;
end;
if (ColumnToRow.Count = 0) or (ParallelismRowValue.Count = 0) then
begin
Result := DtSet;
Exit;
end;
OldFixFlds := TStringList.Create;
NwFldsName := TStringList.Create;
NwFldsType := TStringList.Create;
NwFldsLen := TStringList.Create;
for i := 0 to DtSet.FieldCount - 1 do //DtSet不需要轉置的列
begin
iColumn := ColumnToRow.IndexOf(Trim(DtSet.Fields[i].FieldName));
iRow := ParallelismRowValue.IndexOf(Trim(DtSet.Fields[i].FieldName));
if (iColumn = -1) and (iRow = -1) then
begin
NwFldsName.Add(DtSet.Fields[i].FieldName);
OldFixFlds.Add(DtSet.Fields[i].FieldName);
NwFldsType.Add(IntToStr(Ord(dtSet.Fields[i].DataType)));
NwFldsLen.Add(IntToStr(dtSet.Fields[i].Size));
end;
end;
for i := 0 to ColumnToRow.Count - 1 do //DtSet需要轉置的列,並把值置為列名,而ParallelismRowValue則對應為值
begin
DtSet.First;
while not DtSet.Eof do
begin
if NwFldsName.IndexOf(Trim(DtSet.FieldByName(ColumnToRow[i]).AsString)) = -1 then
begin
NwFldsName.Add(Trim(DtSet.FieldByName(ColumnToRow[i]).AsString));
NwFldsType.Add(IntToStr(Ord(DtSet.FieldByName(ParallelismRowValue[i]).DataType)));
NwFldsLen.Add(IntToStr(DtSet.FieldByName(ParallelismRowValue[i]).Size));
end;
DtSet.Next;
end;
end;
DtGenTmp := TAdoDataSet.Create(nil);
for i := 0 to NwFldsName.Count - 1 do
begin
with DtGenTmp.FieldDefs.AddFieldDef do
begin
Name := NwFldsName[i];
FieldType := TFieldType(StrToInt(NwFldsType[i]));
DataType := FieldType;
if FieldType in [ftString, ftWideString, ftMemo] then //這裡有Bug:主要原因是ftInteger的長度不能設置,其它數據類型也不知道要不要設定長度
size := StrToInt(NwFldsLen[i]);
end;
end;
DtGenTmp.CreateDataSet;
LastValue := TStringList.Create;
dtSet.First;
if (OldFixFlds.Count - 1) < iFrontGrp - 1 then
begin
iNewFrontGrp := 0;
end else
iNewFrontGrp := iFrontGrp - 1;
if not dtSet.IsEmpty then
begin
for i := 0 to iNewFrontGrp do
begin
LastValue.Add('');
end;
end;
while not dtSet.Eof do
begin
DtGenTmp.Append;
WillNewGrp := False;
for i := 0 to iNewFrontGrp do
begin
if LastValue[i] <> dtSet.FieldByName(OldFixFlds[i]).AsString then
begin
WillNewGrp := True;
Break;
end;
end;
if WillNewGrp then
LastValue.Clear;
for i := 0 to OldFixFlds.Count - 1 do //未轉置部分數據
begin
if WillNewGrp and (i <= iNewFrontGrp) then
begin
DtGenTmp.FieldByName(OldFixFlds[i]).Value := dtSet.FieldByName(OldFixFlds[i]).Value;
LastValue.Add(dtSet.FieldByName(OldFixFlds[i]).AsString);
end else
if i > iNewFrontGrp then
DtGenTmp.FieldByName(OldFixFlds[i]).Value := dtSet.FieldByName(OldFixFlds[i]).Value;
end;
for i := 0 to ColumnToRow.Count - 1 do //轉置部分數據
begin
DtGenTmp.FieldByName(Trim(dtSet.FieldByName(ColumnToRow[i]).Value)).Value := dtSet.FieldByName(ParallelismRowValue[i]).Value;
end;
DtGenTmp.Post;
dtSet.Next;
end;
DtGenTmp.First;
FreeAndNil(OldFixFlds);
FreeAndNil(NwFldsName);
FreeAndNil(NwFldsType);
FreeAndNil(NwFldsLen);
FreeAndNil(LastValue);
Result := DtGenTmp;
end;