Dear Experts
Please help to insert duplicate values based on channel name in pipe separator value
DROP TABLE TEMP_TB
CREATE TABLE TEMP_TB
(
T_AGENTID VARCHAR2(20),
T_AGGENT_MOB VARCHAR2(20),
T_CHANNEL VARCHAR2(100)
);
INSERT INTO TEMP_TB VALUES('56854','+985485421','MOB|ATM|POS');
INSERT INTO TEMP_TB VALUES('96521','+985624289','MOB|ATM|POS|MICRO ATM');
CREATE TABLE MAIN_TB
(
M_AGENTID VARCHAR2(20),
M_AGGENT_MOB VARCHAR2(20),
M_CHANNEL VARCHAR2(100)
);
please help me to insert records in main_tb from temp_tb based on the channel
in temp_tb i have three channel 'MOB|ATM|POS' for agentid 56854,so i want to insert 3 records in main_tb
similarly 4 channels 'MOB|ATM|POS|MICRO ATM' for agent 96521
Desired result
SELECT * FROM MAIN_TB
M_AGENTID M_AGGENT_MOB M_CHANNEL
56854 +985485421 MOB
56854 +985485421 ATM
56854 +985485421 POS
96521 +985624289 MOB
96521 +985624289 POS
96521 +985624289 ATM
96521 +985624289 MICRO ATM
Please help