Categories > TinyButStrong general >

crosstab or pivot

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: SunWuKung
Date: 2007-01-11
Time: 17:45

crosstab or pivot

Hi,
is there currently a way to use TBS to present data in a crosstab or pivot format? If yes could somebody provide a simple example using:
rowid, columnid, cellvalue

Thanks for the help.
SWK
By: Skrol29
Date: 2007-01-11
Time: 18:34

Re: crosstab or pivot

Hi,

I'm preparing a plug-in for pivot table. But it will work only with the next TBS version (current version is now TBS 3.2.0).

The only solution I've found for now is to make the cross table the PHP side and then perfome a merge with dynamic columns (like in the on line example).
By: SunWuKung
Date: 2007-01-12
Time: 08:26

Re: crosstab or pivot

If you have an example, could you post it? It would help me with both steps - prepare the pivot in php and than performing the dynamic merge.

Thx.
SWK
By: Skrol29
Date: 2007-01-15
Time: 23:26

Re: crosstab or pivot

This one is available.
  http://www.tinybutstrong.com/dev/rdv.zip

Sorry it is not small, and the interface is in french (the code is in english).
I have nothing else I can publish and which has a pivot example.
By: SunWuKung
Date: 2007-01-19
Time: 10:51

Re: crosstab or pivot

A friend of mine - let me write his name on the wall of fame: Pavlos Stamboulides - came to my help and solved this for me. I would like to share the solution he came up with, because its quite generic, very elegant and may help people:

PHP side:
$sql=<<<HEREDOC
Select row AS rowed, col AS columned, cell AS cellval FROM mytbl ORDER BY row HEREDOC; $res = $db->get_result_array($sql); //this array contains db result - columns are rowid, columnid, cellval

foreach ($res as $r){
    $row = $r['rowid'];
    $col = $r['columnid'];
    $val = $r['cellval'];       
    $table[$row][$col] = $val;
    $allcols[$col]=$col;   
}
asort($allcols);
$TBS->MergeBlock('c0,c1,c2', $allcols);
$TBS->MergeBlock('blk1',$table);

Template side:
<table width="765" border="1" align="center" cellpadding="2" cellspacing="0">
  <tr bgcolor="#CACACA">
    <td><div align="center"><strong>PersonID</strong></div></td>
    <td nowrap="nowrap"><div align="center"><strong>[c0.val;block=td]</strong></div></td>
  </tr>
  <tr bgcolor="#F0F0F0">
    <td><div align="center">[blk1.$;block=tr]</div></td>
    <td nowrap="nowrap"><div align="center">[blk1.[c1.key;block=td];noerr]</div></td>
  </tr>
    <tr bgcolor="#E6E6E6">
    <td><div align="center">[blk1.$;block=tr]</div></td>
    <td nowrap="nowrap"><div align="center">[blk1.[c2.key;block=td];noerr]</div></td>
  </tr>
  <tr bgcolor="#FFCFB9">
    <td colspan="3"><div align="center">[blk1;block=tr;nodata]There is no data. </div></td>
  </tr>
</table>

Hope it helps somebody  - I know I was struggling with this a log.
SWK
By: sheepy
Date: 2007-01-19
Time: 11:46

Re: crosstab or pivot

Yes.  Free your mind from the rigid templates where you either have static template or a template that looks awfully like php.

Welcome to the vibrant, dynamic world of TBS. =)

This is indeed a useful technique. I've lost count of the projects I've used a dynamic column... almost all of them, actually. @_@  My CMS use it to generate data tables.