在Delphi中自己建立交叉表

最新推荐文章于 2019-01-07 23:18:04 发布

Mr.Adam

最新推荐文章于 2019-01-07 23:18:04 发布

阅读量733

点赞数

文章标签： delphi integer sql server dataset string function

本文链接：https://blog.csdn.net/CYRTSOFT/article/details/234790

版权

经常在CSDN上查阅名位大侠的文章，得益不少，近期因做一个项目，需要用到交叉表，报表上倒是有，但客户要求在Grid上能操作，没有办法，只好自己写了一段代码用于普通查询到交叉表的实现，不敢独享，故上传，望能抛砖引玉，请名位大侠不吝指教。

function CreateTmptab(const AFieldDefs:TFieldDefs):TDataSet;
var
TempTable:TatClientDataSet;
begin
TempTable:=nil;
Result:=nil;
if AFieldDefs<>nil then
begin
    try
        TempTable:=TatClientDataSet.Create(Application);
        TempTable.FieldDefs.Assign(AFieldDefs);
        TempTable.CreateDataSet;
        Result:=(TempTable as TDataSet);
    Except
    if TempTable<>nil then
        TempTable.Free;
        Result:=nil;
        raise;
    end
end;
end;
{
SouDataset源数据集
ColField交叉表动态列字段
RowField交叉表行字段
DataField数据字段
}
function GenCrossTable(SouDataset:tdataset;ColField,RowField,DataField:string):tdataset;
var
Vdataset:tdataset;
tmpdataset:tatclientdataset;
DataSource:tdatasource;
tmpstrs:tstrings;
rowval,colval,dataval:string;
i,j:integer;
datatype:TFieldType;
DataSize:integer;
begin
result:=nil;
if (ColField='') or(RowField='')or(DataField='') then
showmessage('All Field not be NULL!')
else
begin
if (ColField=RowField)
      or(ColField=DataField)
      or(RowField=DataField) then
    showmessage('All Field not be Equ!')
else
if (self.SouDataSet.FieldByName(ColField).DataType=ftString)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftWideString)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftFixedChar)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftMemo)
    or (self.SouDataSet.FieldByName(ColField).DataType<>ftFmtMemo) then
begin
try
    tmpstrs:=tstringlist.Create;
    Vdataset:=SouDataSet;
    Vdataset.First;
    for i:=0 to Vdataset.RecordCount-1 do
    begin
      if (varisnull(SouDataSet.FieldValues[colfield])=false) and (SouDataSet.FieldValues[colfield]<>'') then
        if tmpstrs.IndexOf(SouDataSet.FieldValues[colfield])=-1 then
        begin
          tmpstrs.Add(SouDataSet.FieldValues[colfield]);
        end;
      Vdataset.Next;
    end;
    //生成动态列标题
    tmpdataset:=TClientDataSet.Create(Self);
    tmpdataset.FieldDefs.Add(rowfield,ftstring,50,False);
    for i:=0 to tmpstrs.Count-1 do
    begin
      with tmpdataset.FieldDefs do
      begin
        Add(tmpstrs.Strings[i],ftInteger,0,False);
      end;
    end;
    tmpdataset.FieldDefs.Add('Sum',ftInteger,0,False);
    DataSource:=tdatasource.Create(self);
    DataSource.DataSet:=tmpdataset;
    with DataSource do
    begin
      dataset:=Createtmptab(tmpdataset.FieldDefs);
      dataset.Open;
    end;
    //建立临时表
    Vdataset.First;
    for i:=0 to Vdataset.RecordCount-1 do
    begin
      rowval:=SouDataSet.fieldbyname(rowfield).AsString;
      colval:=SouDataSet.fieldbyname(colfield).AsString;
      dataval:=SouDataSet.fieldbyname(datafield).AsString;
      if dataval='' then dataval:='0';
      if DataSource.DataSet.Locate(rowfield,rowval,[loPartialKey]) then
      begin
        DataSource.DataSet.Edit;
        DataSource.DataSet.FieldByName(colval).AsString:=dataval;
        DataSource.DataSet.FieldByName('Sum').AsInteger:=
          DataSource.DataSet.FieldByName('Sum').AsInteger+strtoint(dataval);
        DataSource.DataSet.Post;
      end
      else
      begin
        DataSource.DataSet.Append;
        DataSource.DataSet.FieldByName(rowfield).AsString:=rowval;
        for j:=1 to DataSource.DataSet.Fields.Count-1 do
          DataSource.DataSet.Fields[j].AsCurrency:=0;
        DataSource.DataSet.FieldByName(colval).AsString:=dataval;
        DataSource.DataSet.FieldByName('Sum').AsString:=dataval;
        DataSource.DataSet.Post;
      end;
      Vdataset.Next;
    end;
    result:=DataSource.DataSet;
    //生成交叉表数据集
    tmpstrs.Free;
except
end;
end
else
    showmessage('ColField Must be of Type String!') ;
end;
end;