Split multi values columns into rows column wise

[alirezatb]

Hi,

I have a table with the following structure

State	Area Name	Val1	Val2
AK	Alaska A	44,33,22,66	1,2,3,4
AL	Alabama	11,22,33,44	2,3,4,5

I need to change the data layout to the following

State	Area Name	Val1	Val2
AK	Alaska A	44	1
AK	Alaska A	33	2
AK	Alaska A	22	3
AK	Alaska A	66	4
AL	Alabama	11	2
AL	Alabama	22	3
AL	Alabama	33	4
AL	Alabama	44	5

Any Help is appreciated.

Regards

[AngelosPachis · Accepted solution]

Hi @alirezatb ,

Here are two ways you can do it. The first method assumes that the number of concatenated values under columns Val1 and Val2 are the same for each record.

The second method will address any missing values in one of  the two fields (in this example, I removed one value from Val2 column in the first record

Cheers,

Angelos

[alirezatb · Accepted solution]

Thank you so much.