经常在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;
以上代码在D7和SQL Server 7.0/2000测试通过
