Need a good way to use Delphi to “rotate” (rotate?) Data in a flat table

Question

I have a data table with records in which there is a repeating field "position". Imagine a rotating table with four positions with numbers from 1 to 4 on. I store 4 rows of the database table for each rotation, and each row contains a rotation position from 1 to 4 on, so this position field is 1,2,3,4,1,2,3,4,1,2, etc. d. Example to be:

Position Field1 Field2 Field3 ------------------------------ 1 4.5 4.6 1.4 2 5.5 3.6 2.4 3 4.5 7.6 3.4 4 7.5 2.6 4.4 1 2.5 3.6 5.4 2 3.5 9.6 6.4 3 9.5 3.6 7.4 4 1.5 6.6 8.4 1 2.5 9.6 9.4 

I would like to display a grid (or create a table) that has a position field 1,2,3,4 at the top (columns) and each of my other source data fields (which were columns) are now like rows. In the above example, this would be:

  1 2 3 4 ------------------------------ Field1 2.5 3.5 9.5 1.5 Field2 9.6 9.6 3.6 6.6 Field3 9.4 6.4 7.4 8.4 

I need only the last 4 data records to be displayed, and I think this is a kind of pivot? I have an Express Express' Quantum Grid, so I can easily add my consolidated set if necessary. My requirement is that it should be very fast and display only the last 4 positions, not the amounts or other aggregates. Can I encode this fast? Is this something a reporting tool can do? Or should I use a consolidated set solution?

Answer 1

Here is my attempt to make a decision that I think is quick:

 // // set alignment to 4 bytes, we will need it that way for later // {$A4} type TMyRecord = record Position: Integer; Field1: Double; Field2: Double; Field3: Double; end; const szMyRecord = SizeOf(TMyRecord); const CMYRECORD_LIST: array[0..8] of TMyRecord = ( (Position: 1; Field1: 4.5; Field2: 4.6; Field3: 1.4), (Position: 2; Field1: 5.5; Field2: 3.6; Field3: 2.4), (Position: 3; Field1: 4.5; Field2: 7.6; Field3: 3.4), (Position: 4; Field1: 7.5; Field2: 2.6; Field3: 4.4), (Position: 1; Field1: 2.5; Field2: 3.6; Field3: 5.4), (Position: 2; Field1: 3.5; Field2: 9.6; Field3: 6.4), (Position: 3; Field1: 9.5; Field2: 3.6; Field3: 7.4), (Position: 4; Field1: 1.5; Field2: 6.6; Field3: 8.4), (Position: 1; Field1: 2.5; Field2: 9.6; Field3: 9.4) ); const CDELTA = 4; // last 4 records CCOLUMNS = 4; // columns for target grid procedure|function... type // // used for accessing our record Fields from 1 to 3 as array // TMyRecordHack = array[0..2] of Double; const szInteger = SizeOf(Integer); var Index: Integer; kIndex: Integer; LStartIndex: Integer; LRecFields: TMyRecordHack; LRecAddr: Integer; LMyListAddr: Integer; LCol: Integer; LRow: Integer; begin LStartIndex := Length(CMYRECORD_LIST) - CDELTA; // // store address of first record // LMyListAddr := Integer(@CMYRECORD_LIST[Low(CMYRECORD_LIST)]); // // store the address of TMyRecord at LStartIndex // LRecAddr := // // CMYRECORD_LIST base address // LMyListAddr + // // record address is RecordIndex * szMyRecord // LStartIndex * szMyRecord + // // skip Position member of TMyRecord // szInteger; for Index := LStartIndex to High(CMYRECORD_LIST) do begin // // convert address into pointer and cast pointer as TMyRecordHack // LRecFields := TMyRecordHack( Ptr( LRecAddr )^ ); LCol := CMYRECORD_LIST[ Index ].Position; for kIndex := Low(LRecFields) to High(LRecFields) do begin // // +1 required, we're using a string grid // LRow := kIndex +1; // // display data // StringGrid1.Cells[ LCol, LRow ] := FloatToStr( LRecFields[ kIndex ] ); end; // for kIndex := Low(LRecFields) to High(LRecFields) do begin // // increment LRecAddr by szMyRecord // Inc(LRecAddr, szMyRecord); end; // for Index := LStartIndex to High(CMYRECORD_LIST) do begin end; 

OK, TMyRecordHack may not be the best name, but you can call it whatever you want (: