Multiply the number of rows in a select statement

Is there a way to multiply the number of rows returned of a select statement by means of the value of some column?

I have such a case:

create or replace table mytable(OrderName varchar(10), DeliveryAddress varchar(100), NrOfPackets int);
insert into mytable values
('OrderA', 'AddressA', 1),
('OrderB', 'AddressB', 2),
('OrderC', 'AddressC', 1);

What I want is to have 2X of the second line (OrderB).

The only helpful solution was using union all like this:

select OrderName, DeliveryAddress from mytable where NrOfPackets > 0
union all
select OrderName, DeliveryAddress from mytable where NrOfPackets > 1
order by DeliveryAddress

Is there a better way (without limiting the NrOfPackets)?

union select

asked 01 Feb, 09:03

Baron
2.1k●138●150●178
accept rate: 48%

edited 01 Feb, 09:04

flat view

One Answer:

active answers oldest answers newest answers popular answers

What about a join to the row_generator procedure? select OrderName, DeliveryAddress from mytable join sa_rowgenerator(0,100) on row_num between 1 and NrOfPackets;

Assumes that NrOfPackets <= 100

permanent link

answered 01 Feb, 09:52

Justin Willey
7.6k●137●179●249
accept rate: 20%

or safer:

select OrderName, DeliveryAddress from mytable join sa_rowgenerator(1,(select max(NrOfPackets) from mytable)) on row_num <= NrOfPackets order by OrderName;

(01 Feb, 09:54) Justin Willey

Replies hidden

Thanks for the elegant solution.

(01 Feb, 10:05) Baron

I got a bit obsessive about this - and I asked ChatGPT It suggested another approach using recursive queries which I don't often use: WITH RECURSIVE multiplied_rows (OrderName, DeliveryAddress, NrOfPackets) AS ( SELECT OrderName, DeliveryAddress, NrOfPackets FROM mytable WHERE NrOfPackets > 0 UNION ALL SELECT OrderName, DeliveryAddress, NrOfPackets - 1 FROM multiplied_rows WHERE NrOfPackets > 1 ) SELECT OrderName, DeliveryAddress FROM multiplied_rows ORDER BY DeliveryAddress;

which also seems to work.

(09 Feb, 06:27) Justin Willey

Replies hidden

Comment Text Removed

and then I got really carried away and asked it to illustrate the query!

It came back with this which it described as: Here's an image inspired by the SQL statement you provided, capturing the essence of selecting order names and delivery addresses, combined with the creative process of handling data and organizing it for delivery. This whimsical scene, set in an old-fashioned office bustling with activity, brings the structured query into a vivid, imaginative world.

(09 Feb, 06:40) Justin Willey

Ah, that's the new graphical plan analyzer, nice!

That being said, I'm sure the SQL Anywhere-specific row generator procedure and the ability to use that procedure in a FROM clause are waaayyy more efficient and understandable here... - but what do I know :)

(09 Feb, 06:52) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

select ×69
union ×12

question asked: 01 Feb, 09:03

question was seen: 318 times

last updated: 15 Feb, 06:43

SAP SQL Anywhere

Multiply the number of rows in a select statement

Follow this question

Related questions