I have a problem to create a solution which must generate a duplicate rows. I want to duplicate row N times where N is a column in my example N is a QUANTITY_INVOICED.
QUANTITY_INVOICED can have a plus, minus or zero values. My solution below:
create table invoices_table
(
PRODUKT_NAME VARCHAR(10),
QUANTITY_INVOICED NUMBER
);
INSERT INTO invoices_table VALUES ('NEW SS', 3);
INSERT INTO invoices_table VALUES ('NEW SS', -3);
INSERT INTO invoices_table VALUES ('SS Cortex',3);
INSERT INTO invoices_table VALUES ('Cortex', -3);
INSERT INTO invoices_table VALUES ('Cortex', 0);
SELECT
PRODUKT_NAME,
QUANTITY_INVOICED AS ALL_QUANTITY,
sign(QUANTITY_INVOICED) AS
FROM invoices_table,
(SELECT rownum repeat FROM dual
CONNECT BY LEVEL<=
(SELECT max(QUANTITY_INVOICED) from invoices_table)
)r
WHERE QUANTITY_INVOICED<=r.repeat;
Have you to give an idea of to display how to create a solution which take into account minus and zero values?