<div>
simply try:<br />
<br />
select * from (<br />
select customers.CustomerID, customers.CustomerName, customers.Address from Customers<br />
union all<br />
select customers.CustomerID, customers.CustomerName, customers.Address from Customers<br />
where customers.quantity = 2<br />
) a<br />
&nbsp;order by CustomerName
</div>


<div>
@Ryan -- if quantity = 3 then we have to again add another union all. This is not generic. :)
</div>


<div>
Olaf!!<br />
Great to see you active and more powerful than ever, know you since VFP days :-)<br />
You are right, i didn't mention how i connect and query, it's done with Alpha Anywhere. While your solution cannot be adapted to my usecase ( i need to print a report based on the query result ), i can generate a recordset from the query and &quot;scan&quot; through it, then i'll just ask in the Alpha Anywhere forums how to base report on resulting array.<br />
I will mark this as solution.<br />
<br />
Thank you very much<br />
Jaime
</div>


<div>
I posted my closing comment in the response to the thread
</div>


<div>
Thanks,<br />
<br />
so you're not using PHP. OK. I don't see a way (a simple way) to multiply rows by the value of a single column. In MSSQL I would perhaps cross apply a tally table on value&lt;=Customers.Quantity or something like that, But the simpler idea is to do it on the client side anyway.<br />
<br />
PHP will fetch into an array and you can multiply rows here, You also know VFP will have cursors and how you could act there. Whatever structure you have client side should be able to clone or repeat rows in that manner.<br />
<br />
Bye, Olaf.
</div>


<div>
<blockquote>
if quantity = 3 then we have to again add another union all. This is not generic. :) 
</blockquote>
@Pawan, well, this is true, all based on requirements.
</div>


<div>
<div class="content wysiwyg-content">
Hi<br />
How can i multiply the number of resulting rows in a query based on the value of a field.<br />
<br />
For instance:<br />
select customers.CustomerID, customers.CustomerName, customers.Address from Customers order by CustomerName<br />
&nbsp;<br />
will give me a list of customers. Now, assuming i have a customers.Quantity field, i would like that each resulting row in the query will appear Quantity times, i.e if quantity=2, that customer's row will appear twice<br />
<br />
Thanks<br />
Jaime
</div>
</div>


Hi
How can i multiply the number of resulting rows in a query based on the value of a field.

For instance:
select customers.CustomerID, customers.CustomerName, customers.Address from Customers order by CustomerName
 
will give me a list of customers. Now, assuming i have a customers.Quantity field, i would like that each resulting row in the query will appear Quantity times, i.e if quantity=2, that customer's row will appear twice

Thanks
Jaime

Multiply result rows in a query

MariaDB

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.