People, I have a table in SQL with the following columns "pv, product, values".
Currently I have a registration form, where the person inserts these values and he goes to this table with id auto increment.
But I need to generate a control report ... where each column is a product and in the lines the total appears for pv.
It's currently like this
<% data1 = request.form("data1") %>
<% data2 = request.form("data2") %>
<% SET RS_total = conexao.execute("SELECT pv, produto, SUM(valores) as total FROM [database].[dbo].[tabela] where data between '" &data1& "' and '" &data2& "' group by pv, produto")%>
<table class="table table-striped">
<thead>
<tr>
<th>PV</th>
<th>Produto</th>
</tr>
</thead>
<tbody>
<tr>
<% while not RS_total.eof %>
<td class="total"><%=RS_total("pv")%></td>
<td class="total"><%=FormatNumber(RS_total("total"),2)%></td>
</tr>
<%
RS_total.movenext
wend
RS_total.close
%>
</tbody>
</table>
But I need it to look like this:
<table class="table table-striped">
<thead>
<tr>
<th>PV</th>
<th>Produto 1</th>
<th>Produto 2</th>
<th>Produto 3</th>
// algum loop aqui //
<th>Produto N</th>
</tr>
</thead>
<tbody>
<tr>
<td class="total">PV 1</td>
<td class="total">10</td> //total do produto 1 do PV 1
<td class="total">20</td> // total do produto 2 do PV 1
<td class="total">30</td> // total do produto 3 do PV 1
// algum loop aqui //
<td class="total">50</td> // total do produto n do PV 1
</tr>
<tr>
<td class="total">PV 2</td>
<td class="total">10</td> //total do produto 1 do PV 2
<td class="total">20</td> // total do produto 2 do PV 2
<td class="total">30</td> // total do produto 3 do PV 2
// algum loop aqui //
<td class="total">50</td> // total do produto n do PV 2
</tr>
</tbody>
</table>
is there any way to do this? because if I make a static table, I will have to do at least 2000 queries