Geek Answers Handbook

How to split one row into several rows and paste into a table in Oracle?

I select data from the Employee_Master table using the following query

 SELECT EMP_ID , EMP_NAME , EMP_LOC1 , EMP_LOC2 , EMP_LOC3 , EMP_DESG , EMP_ADDR1 , EMP_ADDR2 , EMP_ADDR3 , EMP_PHNO , EMP_STATUS FROM Employee_Master WHERE EMP_STATUS = 'A'

The result set is as follows.

  EMP_ID EMP_NAME EMP_LOC1 EMP_LOC2 EMP_LOC3 EMP_DESG EMP_ADDR1 EMP_ADDR2 EMP_ADDR3 EMP_PHNO EMP_STATUS 23 Covey, Stephen J, Mr 1 2 4 Writer 3 3 3 11 A 24 Jhon, Abraham A, Mr 1 2 4 Actor 1 1 1 22 A

Now I need to split one record into three records and insert into Emp_history table like this. EMP_SAL_ID is retrieved from another table that I can take care of.

  EMP_SAL_ID First_Name Middle_Initial Last_Name Title Designation Location Address Phone_No Status 3456 Stephen J Covey Mr Writer 1 3 11 A 3456 Stephen J Covey Mr Writer 2 3 11 A 3456 Stephen J Covey Mr Writer 4 3 11 A

Is it possible to achieve this using a PL / SQL block or any other simple method with performance.

+5
source share
3 answers

You can use UNION to have separate lines for each address value.

For example, let's say you have a line built in a with clause:

 SQL> WITH DATA(EMP_NAME,EMP_ADDR1,EMP_ADDR2,EMP_ADDR3) AS( 2 SELECT 'Covey, Stephen J, Mr', 1, 2, 4 FROM DUAL UNION ALL 3 SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL 4 ) 5 SELECT * FROM DATA; EMP_NAME EMP_ADDR1 EMP_ADDR2 EMP_ADDR3 -------------------- ---------- ---------- ---------- Covey, Stephen J, Mr 1 2 4 Jhon, Abraham A, Mr 1 2 4 SQL>

Now you can split the specified string into multiple lines using UNION . Another effort is to use SUBSTR and INSTR to extract the name from emp_name.

For instance,

 SQL> WITH DATA(EMP_NAME,EMP_ADDR1,EMP_ADDR2,EMP_ADDR3) AS( 2 SELECT 'Covey, Stephen J, Mr', 1, 2, 4 FROM DUAL UNION ALL 3 SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL 4 ) 5 SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)) AS "ename", 6 emp_addr1 AS "addr" 7 FROM DATA 8 UNION ALL 9 SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)), 10 emp_addr2 11 FROM DATA 12 UNION ALL 13 SELECT SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)), 14 emp_addr3 15 FROM DATA 16 / ename addr -------------------- ---------- Stephen 1 Abraham 1 Stephen 2 Abraham 2 Stephen 4 Abraham 4 6 rows selected. SQL>

NOTE :

The WITH clause is used only to create sample data for a demonstration purpose. In the real case, you just need to use the SELECT statement in your table.

 INSERT INTO hist_table SELECT statement as shown above...
+2
source 
 create table adress_test(ename varchar2(30), addr number); insert all into adress_test values(SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)), emp_addr1) into adress_test values(SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)),emp_addr2) into adress_test values(SUBSTR(emp_name, instr(emp_name, ',', 1, 1)+1, instr(emp_name, ' ', 1, 2) - instr(emp_name, ',', 1, 1)),emp_addr3) SELECT 'Covey, Stephen J, Mr' EMP_NAME , 1 EMP_ADDR1, 2 EMP_ADDR2, 4 EMP_ADDR3 FROM DUAL UNION ALL SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL ;
0
source

As an answer option from Lalit:

 WITH DATA(EMP_NAME,EMP_ADDR1,EMP_ADDR2,EMP_ADDR3) AS( SELECT 'Covey, Stephen J, Mr', 1, 2, 4 FROM DUAL UNION ALL SELECT 'Jhon, Abraham A, Mr ', 1, 2, 4 FROM DUAL ), a as (select 1 as n from dual union all select 2 as n from dual union all select 3 as n from dual) select emp_name, case an when 1 then emp_addr1 when 2 then emp_addr2 when 3 then emp_addr3 end as emp_addr from data inner join a on 1=1

This may be more efficient for large tables. A large table (data) should be scanned only once.

0
source

More articles:


All Articles