Multiply result rows in a query

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
simply try:

select * from (
select customers.CustomerID, customers.CustomerName, customers.Address from Customers
union all
select customers.CustomerID, customers.CustomerName, customers.Address from Customers
where customers.quantity = 2
) a
 order by CustomerName
Pawan KumarDatabase ExpertCommented:
@Ryan -- if quantity = 3 then we have to again add another union all. This is not generic. :)
Olaf DoschkeSoftware DeveloperCommented:
You're not telling how you connect and query, let me assume PDO. then you could act this way:

$connect = new PDO($dsn, $user_name, $pass_word);

$sql = "Select * FROM Customers";	

$result = $connect->prepare($sql);

//bind parameter(s) to variable(s)
//$result->bindParam( . . . );

$status = $result->execute();

if (($status) && ($result->rowCount() > 0))
	$results = array();

	// fetch single rows multiplying with Customers.quantity
	while ($row = $result->fetch(PDO::FETCH_ASSOC))
           for ($i=0; $i<$row['quantity']; $i++)
		   $results[] = $row;

	//dump all data from associative array converted from query result


$connect = null;

Open in new window

Bye, Olaf.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

GreatSolutionsC.I.OAuthor Commented:
Great to see you active and more powerful than ever, know you since VFP days :-)
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 "scan" through it, then i'll just ask in the Alpha Anywhere forums how to base report on resulting array.
I will mark this as solution.

Thank you very much
GreatSolutionsC.I.OAuthor Commented:
I posted my closing comment in the response to the thread
Olaf DoschkeSoftware DeveloperCommented:

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<=Customers.Quantity or something like that, But the simpler idea is to do it on the client side anyway.

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.

Bye, Olaf.
Ryan ChongCommented:
if quantity = 3 then we have to again add another union all. This is not generic. :)
@Pawan, well, this is true, all based on requirements.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.