[CLOSED] Are there any limit to field numbers in TBufDataSet?

[Thaddy]

So, the issue actually is transposing from Rows to columns....*sigh*

That is not that strange: the correct name is a pivot table. Which is not uncommon. Delphi once came with such component. It is also in e.g. Excel. The limits are defined by available memory and the high value of the ordinal type used and squared divided by the size of a single record.
I expect a table of, say 3500 rows can easily be translated to a 90 degree pivot table in memory on modern hardware. If it is sane is another question...
What I describe as a pivot table is actually the simplest transform it can do. Pivot tables, as a concept, can do way more.
Most SQL dialects support this.

Meaning I think you are right, Zvoni: it is a common operation.

[Zvoni]

So, the issue actually is transposing from Rows to columns....*sigh*

That is not that strange: the correct name is a pivot table. Which is not uncommon. Delphi once came with such component. It is also in e.g. Excel. The limits are defined by available memory and the high value of the ordinal type used and squared divided by the size of a single record.
I expect a table of, say 3500 rows can easily be translated to a 90 degree pivot table in memory on modern hardware. If it is sane is another question...
What I describe as a pivot table is actually the simplest transform it can do. Pivot tables, as a concept, can do way more.
Most SQL dialects support this.

Meaning I think you are right, Zvoni: it is a common operation.

Err....right.....that....
I always confuse transposing with pivot (as i know it from Excel).
Probably because i'm used to aggregation happening when i pivot in Excel (which in OP's case is NOT the case as i understand it. No aggregation there)

[Thaddy]

No? It is a simple rotate, where pivots in general allow for any dimension. It is still a pivot.
Rubic's cube anyone? 
It is a transposition on any dimension of the data. One of those is transpose x,y to y,x.
Rubic uses x,y,z and challenges you to perform the least of the possible transpositions to solve his puzzle.

[alpine]

Here is a CTE select that will do the job :

Code: SQL  [Select][+][-]

1. WITH
2. bufdataset1(ID, fieldname, val) -- <------- That must be the real table with data
3. AS (
4.   VALUES
5.   ( 001, 'F1',  1),
6.   ( 001, 'F2',  4),
7.   ( 001, 'F3',  12),
8.   ( 001, 'F20', 202),
9.   ( 001, 'F35', 998),
10.   ( 002, 'F1',  5),
11.   ( 002, 'F2',  3),
12.   ( 002, 'F3',  32),
13.   ( 002, 'F33', 333),
14.   ( 002, 'F35', 999),
15.   ( 003, 'F12', 42)
16. ),
17. flds(N, fn)
18. AS (
19.   VALUES(1, 'F1')
20.   UNION ALL
21.   SELECT
22.     N + 1, 'F' || CAST(N + 1 AS TEXT)
23.   FROM
24.     flds
25.   LIMIT 35 -- <------------- Here limit must be adjusted accordingly (3500?)
26. ),
27. tablerows(M)
28. AS (
29.   VALUES(1)
30.   UNION ALL
31.   SELECT
32.     M + 1
33.   FROM
34.     tablerows
35.   WHERE
36.     M < (SELECT MAX(ID) FROM bufdataset1)
37. )
38. SELECT
39.   N x, M y, fn, val
40. FROM
41.   tablerows, flds LEFT OUTER JOIN bufdataset1 ON
42.     tablerows.M = bufdataset1.ID AND flds.fn = bufdataset1.fieldname
43. ORDER BY
44.   tablerows.M, flds.N

Of course, bufdataset1 must be an existing table as described in reply #8, not as a CTE which here is just for the example. Also the LIMIT for flds should be adjusted as needed.

It gives a result like:

Code: [Select]

x      y        fn     val
1 1 F1 1
2 1 F2 4
3 1 F3 12
4 1 F4 NULL
5 1 F5 NULL
6 1 F6 NULL
...
35      3       F35     NULL
The rows count will be N*M thus covering the whole spreadsheet as required. The only thing needed is to iterate over it and use x,y in e.g.

Code: Pascal  [Select][+][-]

1. FPSpreadsheet.TsWorksheet.WriteNumber(y, x, val, ...)

[egsuh]

Thank you for your interests and advises, but please do not waste your time and cognitive resources.

Ordering the first table is not simple. The ordering should be based on 64 or so characters long string. They are not in the order of field name. Each fieldname has to refer to another data structure (in my case TFPGMap).  I can define fields  (in correct order) of the second table from the TFPGMap (dynamically). Following is my real codes. It directly writes to text file, instead of creating any memory-residing structure.

In the beginning I used BufDataSet as the target (bufdataset2 in the previous example), and did not have any problem with most questionnaires for 5 years, until a new project's total column count reached 2000 or something. A solution that hit me first was to expand the column number limit of a table. I don't think it is possible with TBufDataSet, so I thought of SQLite3, and I found it says I can expand the column number limit by recompiling the sources, which is not a very good idea actually. I have to distribute sqlite3.dll with every copy of my application.

Then I tested ordering the first table (bufdataset1 in the previous example, and temptable in following example).  I was afraid that it may be time-consuming if the number of rows of the first table is very large. As result, not that bad when I applied "filter". Most of the table cells are sparse, and number of rows of each ID is not large in most cases.  Then, the program writes the content to a file on the disk.

There could be improvements on the performance of following codes, but that does not deserve the effort from business point of view. This runs on terminal, neither DB nor Web server.

Many thanks again for the warmest concerns.

Code: Pascal  [Select][+][-]

1.       tcount := 0;
2.  
3.       tempTable.IndexFieldNames:= 'RID;VarOrderStr;ROrder';
4.       tempTable.Filtered:= True;
5.  
6.       AssignFile(f, fname);
7.       Rewrite(f);
8.  
9.       write(f, 'SID,RID,STATUS,IVID,Start,Last,');              
10.       for ti := 0 to OrderedVarList.Count-1 do begin
11.          AVar := OrderedVarList.Data[ti];
12.          if AVar.MaxCount + AVar.MaxOpenCount = 1
13.             then Write(f, AVar.VarName(0), ',')                    
14.             else begin
15.                for tj := 1 to AVar.MaxCount do Write(f, AVar.VarName(tj), ',');
16.                for tj := 1 to AVar.MaxOpenCount do Write(f, AVar.VarName(-tj), ',');
17.             end;
18.       end;
19.       Writeln(f);
20.  
21.       trRes.First;
22.       while not trRes.Eof do begin
23.          with trRes do begin
24.             trid:= FieldByName('rid').AsInteger;
25.             ts:= Format('%s,%d,%s,%s,"%s","%s",',
26.                  [FieldByName('sid').AsString,
27.                   trid,
28.                   FieldByName('Status').AsString,
29.                   FieldByName('Ivid').AsString,
30.                   FieldByName('Created').AsString,
31.                   FieldByName('LTime').AsString]);
32.          end;
33.          write(f, ts);
34.  
35.          TempTable.Filter:= Format('RID=%d', [trid]);
36.  
37.          tempTable.first;
38.          ti := 0;
39.          ts := tempTable.FieldByName('VarOrderStr').AsString ;
40.          while (not tempTable.Eof) and (ti < OrderedVarList.Count) do begin
41.              AVar := OrderedVarList.Data[ti];
42.              tss := OrderedVarList.Keys[ti];
43.              for tj := 1 to AVar.MaxCount do begin
44.                  if (ts = tss) then begin
45.                      write (f, temptable.FieldByName('rcode').AsInteger);
46.                      temptable.next;
47.                      if temptable.eof then break
48.                      else ts := tempTable.FieldByName('VarOrderStr').AsString ;
49.                  end;
50.                  write (f, ',');
51.              end;
52.  
53.              if tempTable.EOF then break;
54.              for tj := 1 to AVar.MaxOpenCount do begin
55.                  if (ts = tss) then begin
56.                      write (f, temptable.FieldByName('rcode').AsInteger);
57.                      temptable.next;
58.                      if temptable.eof then break
59.                      else ts := tempTable.FieldByName('VarOrderStr').AsString ;
60.                  end;
61.                  write (f, ',');
62.              end;
63.              inc(ti);
64.          end;
65.  
66.          writeln(f);
67.          trRes.Next;
68.          tcount += 1;
69.          if Assigned(SendMessage) then
70.             if (tcount mod 50) = 0 then SendMessage(Format('%d respondents processed', [tcount]));
71.          Application.ProcessMessages;
72.       end;
73.       Close(f);
74.