By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST ( TITLE VARCHAR2(199 BYTE), AMOUNT NUMBER, VALUE NUMBER )
INSERT INTO TEST ( TITLE, AMOUNT, VALUE ) VALUES ( 'Switch', 3000, 12);
1 rows affected
WITH data (title, amount, value, idx) AS (
SELECT title, amount, value, 1
FROM test
UNION ALL
SELECT title, amount, value, idx + 1
FROM data
WHERE idx < value
) SEARCH DEPTH FIRST BY title SET order_num
SELECT title,
LISTAGG(amount * idx, ' ') WITHIN GROUP (ORDER BY idx) AS amounts,
SUM(amount*idx) AS total
FROM data
GROUP BY title;
| TITLE | AMOUNTS | TOTAL |
|---|---|---|
| Switch | 3000 6000 9000 12000 15000 18000 21000 24000 27000 30000 33000 36000 | 234000 |
SELECT t.title,
l.amounts,
t.amount * t.value * (t.value + 1) / 2 AS total
FROM test t
CROSS JOIN LATERAL (
SELECT LISTAGG(LEVEL * t.amount, ' ') WITHIN GROUP (ORDER BY LEVEL) AS amounts
FROM DUAL CONNECT BY LEVEL <= t.value
) l;
| TITLE | AMOUNTS | TOTAL |
|---|---|---|
| Switch | 3000 6000 9000 12000 15000 18000 21000 24000 27000 30000 33000 36000 | 234000 |