how to convert column value to multiple insert rown oracle cursor-CodePudding

I am trying to copy value from our old db to new db where there is a change in the table structure. Below is the structure of the table

Table1

Table1ID, WheelCount, BlindCount, OtherCount

For eg values of table 1 is like below

Table1ID, 1,2,5

Table1 is now changed to TableNew with the below

TableNewID, DisableID , Quantity. So the value should be

TableNewID1, 1,1   here 1= WheelCount from table1
TableNewID2, 2,2   here 2= BlindCount
TableNewID3, 3,5   here 5= OtherCount

how to write a cursor to transform table1 value to the new table tableNew structure.

Expected structure

    ID     Table1ID  DISABLEID   QUANTITY

     1      1    1          1
     2      1    2          1
     3      1    3          5
     4      2    1          8
     5      2    2          10
     6      2    3          15

CodePudding user response：

The simplest is a UNION ALL for each column you want to turn into a row.

insert into tablenew
select table1id,1,wheelcount from table1
union all
select table1id,2,blindcount from table1
union all
select table1id,3,othercount from table1

There are other, sleeker methods for avoiding multiple passes on the first table, in case it's huge.

CodePudding user response：

This is how I understood it.

Current table contents:

SQL> SELECT * FROM table1;

        ID WHEELCOUNT BLINDCOUNT OTHERCOUNT
---------- ---------- ---------- ----------
         1          1          2          5
         2          8         10         15

Prepare new table:

SQL> CREATE TABLE tablenew
  2  (
  3     id          NUMBER,
  4     disableid   NUMBER,
  5     quantity    NUMBER
  6  );

Table created.

Sequence (will be used to populate tablenew.id column):

SQL> CREATE SEQUENCE seq_dis;

Sequence created.

Trigger (which actually populates tablenew.id):

SQL> CREATE OR REPLACE TRIGGER trg_bi_tn
  2     BEFORE INSERT
  3     ON tablenew
  4     FOR EACH ROW
  5  BEGIN
  6     :new.id := seq_dis.NEXTVAL;
  7  END;
  8  /

Trigger created.

Copy data:

SQL> INSERT INTO tablenew (disableid, quantity)
  2     SELECT a.id, a.wheelcount
  3       FROM table1 a
  4     UNION ALL
  5     SELECT a.id, a.blindcount
  6       FROM table1 a
  7     UNION ALL
  8     SELECT a.id, a.othercount
  9       FROM table1 a;

6 rows created.

Result:

SQL> SELECT * FROM tablenew;

        ID  DISABLEID   QUANTITY
---------- ---------- ----------
         1          1          1
         2          2          8
         3          1          2
         4          2         10
         5          1          5
         6          2         15

6 rows selected.

SQL>