I have a table structure where there are multiple "/" separated values in two columns that I need to parse out into single records.
CREATE TABLE CONFIGNEW(PlanID VARCHAR(100), GroupID VARCHAR(6), SubGroupID VARCHAR(255), AddOnCode VARCHAR(2), ExternalCode VARCHAR(20)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '1', 'M231_1)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '2', 'M231_2)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '3', 'M231_1)
INSERT INTO CONFIGNEW(PlanID, GroupID, SubGroupID, ExternalCode) VALUES('101/201', '000005', 'LAA/OCA/UCA/XCA', '4', 'M231_2)
The results I am looking to achieve are:
PLanID GroupID SubGroupID AddOnCode ExternalCode
101 000005 LAA 1 M231_1
101 000005 OCA 2 M231_2
101 000005 UCA 3 M231_3
101 000005 XCA 4 M231_4
201 000005 LAA 1 M231_1
201 000005 OCA 2 M231_2
201 000005 UCA 3 M231_3
201 000005 XCA 4 M231_4
Is there an SQL statement that can be used to accomplish this?