Display SQL results as crosstab

[tboyer]

Anyone have any thoughts about how to use Texis/Vortex to display sql results in a crosstabulated table?

I think I can solve the problem if I can figure out a way to pad SQL output so that empty values get filled with zeros or something.

I.e. something like this:

<table>
<tr>
<tr><th><th> Cars <th> Trucks
<tr><td>Tom<td>0 <td>2
<tr><td>Joe<td>10 <td>20
<tr><td>Lucy<td>3 <td>15
</table>

Thanks.

Tom Boyer

[mark]

<table>
<tr><th>Name</th><th> Cars </th><th> Trucks </th></tr>
<sql row "select Name,Cars,Trucks from sometable">
<tr><td>$Name</td><td>$Cars</td><td>$Trucks</td></tr>
</sql>
</table>

HTML tables take care of alignment. Why do you need padding? Anyhow, you could check for empty with
<if $fieldname eq ''>handle empty<else>$fieldname</if>

[tboyer]

Thanks Mark, that got me thinking enough to find a solution, which I'll post below. There is probably something more elegant than this -- consider this a first shot.

The key to it is the select distincts to set up the rows and columns. I don't like the inefficiency of the repeated selects within two layers of loops. But maybe if you pre-select your results into a ram table and run this crosstab from that, it wouldn't be so expensive to run?


<sql "select distinct Rowfield rows from table order by rows"></sql>
<sql "select distinct Colfield cols from table order by cols"></sql>

<table border = 1>
<tr><th>
<loop $cols>
<th>$cols
</loop>
<loop $rows>
<tr><th>$rows
<loop $cols>
<$cnt=0>
<sql row "select count(*) cnt from table where Rowfield=$rows and Colfield=$cols"></sql>
<if $cnt gt 0>
<td>$cnt
<else>
<td>&nbsp;
</if>
</loop>
</tr>
</LOOP>

</table>