Parsing String with multiple values into single column table

[Maxim_Skorik]

Hi colleagues,

could you please help me with suggestion how to realize one task.

We have in database table with next values:

OurOrderNum	CustomerOrderNum	weight	price	DeliveryDate
abc123	40001;40002;123;1321	1000	2000	21.05.2019
abc124	3434; 23422;40003;40004	2000	3500	23.05.2019
abc125	40005	1500	3000	25.05.2019

So in reference field we have some 'customer orders' grouped into 1 our internal order. There could be unlimited quantity of customer orders grouped into 1 internal order.

For further analysis we need to have table in a way where 1 line = 1 CustomerOrderNum and other columns just duplicated accordingly. For example:

CustomerOrderNum	OurOrderNum	weight
40001	abc123	1000
40002	abc123	1000
123	abc123	1000
1321	abc123	1000
3434	abc124	2000
23422	abc124	2000
40003	abc124	2000
40004	abc124	2000
40005	abc125	1500

So can you please suggest me how to prepare Load statement to parse field CustomerOrderNum (which is separated with ";" and could be with variable length and with variable CustomerOrderNum quantity per line), in the way that we receive resulting table like specified above.  

Thank you! 

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD SubField(CustomerOrderNum, ';') as CustomerOrderNum,
	 OurOrderNum,
	 weight,
	 price,
	 DeliveryDate;
LOAD * INLINE [
    OurOrderNum, CustomerOrderNum, weight, price, DeliveryDate
    abc123, 40001;40002;123;1321, 1000, 2000, 21.05.2019
    abc124, 3434; 23422;40003;40004, 2000, 3500, 23.05.2019
    abc125, 40005, 1500, 3000, 25.05.2019
];

[Maxim_Skorik]

Thank you so much! Exactly what required!