Loop Record in Oracle-CodePudding

I have a Table called TaxAmount. It has 3 columns(ID, Year, Amount). refer the below image.

I want to divide each row into 12 months. I attached a sample image below.

I'm new in Oracle side. please help me to write a Oracle Query to display the above result.

I tried ROWNUM. But No luck.

CodePudding user response：

Here's one option:

SQL> select id, year, column_value as month, amount
  2  from taxamount cross join
  3    table(cast(multiset(select level from dual
  4                        connect by level <= 12
  5                       ) as sys.odcinumberlist))
  6  order by id, year, month;

        ID       YEAR      MONTH     AMOUNT
---------- ---------- ---------- ----------
         1       2022          1        100
         1       2022          2        100
         1       2022          3        100
         1       2022          4        100
         1       2022          5        100
         1       2022          6        100
         1       2022          7        100
         1       2022          8        100
         1       2022          9        100
         1       2022         10        100
         1       2022         11        100
         1       2022         12        100
         2       2022          1        200
         2       2022          2        200
         2       2022          3        200
         2       2022          4        200
         2       2022          5        200
         2       2022          6        200
         2       2022          7        200
         2       2022          8        200
         2       2022          9        200
         2       2022         10        200
         2       2022         11        200
         2       2022         12        200
         3       2022          1        150
         3       2022          2        150
         3       2022          3        150
         3       2022          4        150
         3       2022          5        150
         3       2022          6        150
         3       2022          7        150
         3       2022          8        150
         3       2022          9        150
         3       2022         10        150
         3       2022         11        150
         3       2022         12        150

36 rows selected.

SQL>