学习(编程技巧_编程知识_程序代码),是学习编程不可多得的学习精验

				在Delphi中自己建立交叉表
				 
				
				--------------------------------------------------------------------------------
				 
				szlifei 时间：2003-8-9 12:10:33 
				   
				    经常在CSDN上查阅名位大侠的文章，得益不少，近期因做一个项目，需要用到交叉表，报表上倒是有，但客户要求在Grid上能操作，没有办法，只好自己写了一段代码用于普通查询到交叉表的实现，不敢独享，故上传，望能抛砖引玉，请名位大侠不吝指教。
				
				
				function CreateTmptab(const AFieldDefs:TFieldDefs):TDataSet;
				var
				TempTable:TatClientDataSet;
				begin
				TempTable:=nil;
				Result:=nil;
				if AFieldDefsnil then
				begin
				    try
				        TempTable:=TatClientDataSet.Create(Application);
				        TempTable.FieldDefs.Assign(AFieldDefs);
				        TempTable.CreateDataSet;
				        Result:=(TempTable as TDataSet);
				    Except
				    if TempTablenil 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).DataTypeftWideString)
				    or (self.SouDataSet.FieldByName(ColField).DataTypeftFixedChar)
				    or (self.SouDataSet.FieldByName(ColField).DataTypeftMemo) 
				    or (self.SouDataSet.FieldByName(ColField).DataTypeftFmtMemo)  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;
				
				
				以上代码在D7和SQL Server 7.0/2000测试通过