I often use a lot of the famous heroes in 9CBS. I have to benefit a lot. I have to use a crosstalk because of a project. The report is there, but the customer requires that it can operate on the Grid, there is no way, I have to write it yourself. A code is used in the implementation of ordinary query to the crosstab, not exclusive, so the upper pass, hope to throw the jade, please heroes.
function CreateTmptab (const AFieldDefs: TFieldDefs): TDataSet; varTempTable: TatClientDataSet; beginTempTable: = nil; Result: = nil; if AFieldDefs <> nil thenbegin try TempTable: = TatClientDataSet.Create (Application); TempTable.FieldDefs.Assign (AFieldDefs) Temptable.created; result: = (Temptable as tdata); Except if Temptable <> nil damptable.free; result: = nil; raise; endnd; end; {Soudataset source data set colfield crossted Dynamic column field Rowfield crosstab row fields DataField data field} function GenCrossTable (SouDataset: tdataset; ColField, RowField, DataField: string): tdataset; varVdataset: tdataset; tmpdataset: tatclientdataset; DataSource: tdatasource; tmpstrs: tstrings; rowval, colval, dataval: string; i, J: Integer; DataType: TFIELDTYPE; DATASIZE: INTEGER; BeginResult: = nil; if (colfield = '') or (DataField = ') The showMessage (' All Field Not Be null! ') Elsebegin if (colfield = rowfield) or (rowfield = datafield) Then ShowMessage ('All Field Not Be Equ!') E lse 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 dam tmpsTrs.add (Soudataset.fieldValues [colfield]); END; vDataSet.next; end; // Generate dynamic column 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; // Establish temporary table vDataSet.first; for i: = 0 to vdataSet.Recordcount-1 do begin rowval: = Soudataset.fieldByname (RowField) .sstring; colval: = Soudataset.fieldByname (Colfield) .sstring; dataval: = Soudataset.fieldByname (datafield) .sstring; if Dataval = '' TEN DATAVAL: = '0'; if DataSource.d ataSet.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) .sstring: = dataVal;
DataSource.DataSet.fieldByname ('sum'). Asstring: = dataval; datasource.dataset.post; end; vDataSet.next; end; result: = Datasource.DataSet; // Generate cross-table dataset TmpSTRS.FREE; Except End ELSE SHOWMESSAGE ('Colfield Must Be of Type!); End; end; above code passed by D7 and SQL Server 7.0 / 2000 test
