Value Calculation on oracle sql-CodePudding

I have this table:

CREATE TABLE TEST
(
    TITLE  VARCHAR2(199 BYTE),
    AMOUNT NUMBER,
    VALUE  NUMBER
)

and this INSERT statement:

INSERT INTO TEST (TITLE, AMOUNT, VAL) 
VALUES (Switch, 3000, 12); 
COMMIT;

We have an amount = 3000 up to 12, now we need to calculate.

3000 multiplied by 1 = 3000
3000 multiplied by 2 = 6000
3000 multiplied by 3 = 9000
3000 multiplied by 4 = 12000
3000 multiplied by 5 = 15000
3000 multiplied by 6 = 18000
3000 multiplied by 7 = 21000
3000 multiplied by 8 = 24000
3000 multiplied by 9 = 27000
3000 multiplied by 10 = 30000
3000 multiplied by 11 = 33000
3000 multiplied by 12 = 36000

Regards

Output is needed in the following format.

Title    Amount                                                     Total

Switch   30000  3000 6000 9000  12000 15000 18000 21000 24000 27000 30000 33000 36000   231000
plug
board

Can somebody help me how to get this output in SQL?

CodePudding user response：

You can use a recursive query:

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, amount * idx AS value
FROM   data;

Or a correlated hierarchical query:

SELECT t.title, t.amount * l.idx AS value
FROM   test t
       CROSS JOIN LATERAL (
         SELECT LEVEL AS idx FROM DUAL CONNECT BY LEVEL <= t.value
       ) l;

Which, for the sample data:

CREATE TABLE TEST ( TITLE VARCHAR2(199 BYTE), AMOUNT NUMBER, VALUE NUMBER )
INSERT INTO TEST ( TITLE, AMOUNT, VALUE ) VALUES ( 'Switch', 3000, 12);

Both output:

TITLE	VALUE
Switch	3000
Switch	6000
Switch	9000
Switch	12000
Switch	15000
Switch	18000
Switch	21000
Switch	24000
Switch	27000
Switch	30000
Switch	33000
Switch	36000

fiddle

Or for your output format:

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;

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;

Which both output:

TITLE	AMOUNTS	TOTAL
Switch	3000 6000 9000 12000 15000 18000 21000 24000 27000 30000 33000 36000	234000

fiddle

CodePudding user response：

Try it like here:

WITH
    tbl AS
        (
            Select 'Switch' "TITLE", 3000 "AMOUNT", 12 "VAL" From Dual
        )
--
Select     TITLE, AMOUNT, TOTAL
From      (Select LEVEL "ID", TITLE "TITLE", Sum(AMOUNT * LEVEL) OVER() "TOTAL",
                  LISTAGG(AMOUNT * LEVEL, ' ') WITHIN GROUP (ORDER BY LEVEL) OVER() "AMOUNT"
          From    tbl
          Connect By LEVEL <= VAL )
Where ID = 1  
--  
--  R e s u l t :
--  TITLE   AMOUNT                                                                 TOTAL
--  ------- --------------------------------------------------------------------- ------
--  Switch  3000 6000 9000 12000 15000 18000 21000 24000 27000 30000 33000 36000    234000