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)?

asked 01 Feb, 09:03

Baron's gravatar image

Baron
2.1k134145174
accept rate: 46%

edited 01 Feb, 09:04


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%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

1

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
Comment Text Removed
1

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
1

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
Your answer
toggle preview

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

Question tags:

×69
×12

question asked: 01 Feb, 09:03

question was seen: 188 times

last updated: 15 Feb, 06:43