"Transpose" table in classic ASP

0

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

    
asked by anonymous 28.06.2018 / 22:40

0 answers