generate serial number in decreasing order given a variable in netezza aginity sql

Question

Is there any SQL syntax using netezza SQL, given column number, trying to generate rows for number in decreasing order down to 0.

Below is an example of what I'm trying to do

BEFORE

ID	NUMBER
A	4
B	5

AFTER

ID	NUMBER
A	4
A	3
A	2
A	1
B	5
B	4
B	3
B	2
B	1

please also click to see screenshot for example thanks

Answer 1

You can use the _v_vector_idx table for this purpose

select 
  id, idx 
from 
  test join _v_vector_idx 
     on idx <= number
order
  by id asc, idx desc ;

Here's the example in action

select * from test
  ID   | NUMBER
-------+--------
 A     |      4
 B     |      5
(2 rows)


select id, idx from test join _v_vector_idx on
   idx <= number order by id asc, idx desc ;
  ID   | IDX
-------+-----
 A     |   4
 A     |   3
 A     |   2
 A     |   1
 A     |   0
 B     |   5
 B     |   4
 B     |   3
 B     |   2
 B     |   1
 B     |   0
(11 rows)

insert into test values ('C', 3);
INSERT 0 1

select * from test;
  ID   | NUMBER
-------+--------
 A     |      4
 B     |      5
 C     |      3
(3 rows)

select id, idx from test join _v_vector_idx
  on idx <= number order by id asc, idx desc ;
  ID   | IDX
-------+-----
 A     |   4
 A     |   3
 A     |   2
 A     |   1
 A     |   0
 B     |   5
 B     |   4
 B     |   3
 B     |   2
 B     |   1
 B     |   0
 C     |   3
 C     |   2
 C     |   1
 C     |   0
(15 rows)