Multiply the number of rows in a select statement

[Baron]

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

[VolkerBarth]

Dear AI, can you please show a sample for a cross join with 1000 rows? I don't want to type those one thousand UNION SELECT n query blocks by hand. 🙂

[Baron]

SELECT 1 AS multiplier UNION SELECT 2 UNION SELECT 3.... union select {n}

does nothing more than:

select row_num from sa_rowgenerator(1,n)

Moreover, I dont have experience with CROSS JOIN, So the best solution in my case was this:

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

[Baron]

@Ingenx, moreover, your example is missing an important where condition, so with CROSS JOIN could it work like this:

select OrderName, DeliveryAddress from mytable
cross join
(select * from sa_rowgenerator (1,(select max(NrOfPackets) from mytable))) TableWithRowNumbers
where nrofpackets >= row_num
order by DeliveryAddress 

[VolkerBarth]

@Baron, a CROSS JOIN is basically a join without a join condition - in order to combine each and every row from both joined rowsets. As you do have an existing join condition here, your usage of an INNER JOIN seems much more reasonable IMHO. 🙂

[justin_willey · Accepted solution]

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

[justin_willey · Accepted solution]

or safer:

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

[Baron · Accepted solution]

Thanks for the elegant solution.

[justin_willey · Accepted solution]

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.

[justin_willey · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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 🙂