CODEWITHSUNDEEP
oracle-database
payel ghosh
payel ghosh

Reputation: 51

create another row from one single row in oracle

How to create another row from a single row in oracle? Suppose, i have a table CALLTEST.

Dataset is like:

call_id   PARENT_CALL   OWNER   ACCOUNT_ID    PARENT_CL_OWNER
C3        P3            O4      HCP2           O5
C2        P2            O3      HCP1           O6

i have to create an another data from one data each, so total 4 dataset needs to be generated. conditions are:

  1. owner field has to be updated with the parent call owner

  2. ACCOUNT_ID needs to be updated with null value for the second record.

  3. call_id will be replaced with _1 value.

dataset would be like this:

call_id   PARENT_CALL   OWNER   ACCOUNT_ID    PARENT_CL_OWNER
C3        P3            O5      HCP2           O5
C3_1      P3            O5                     O6
C2        P2            O6      HCP1           O6
C2_1      P2            O6                     O6

so please help the exact sql query or the procedure block for achieving this scenerio.

Upvotes: 0

Views: 49

Answers (1)

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

SELECT   call_id,
         parent_call,
         parent_cl_owner owner,
         account_id,
         parent_cl_owner
  FROM   calltest
UNION ALL
SELECT   call_id || '_1',
         parent_call,
         parent_cl_owner,
         NULL,
         parent_cl_owner
  FROM   calltest
ORDER BY   call_id

Upvotes: 1

Related Questions