There is a workaround to solve this. Is not a definitive solutions but it can help: The final result would be like this:  Firstly, I created a table like your example (I used ";" as separator): insert overwrite t_1 select 'Asia' as cont,'Japan;China;Singapore;' Country_list union select 'Europe' as cont,'UK;Spain;Italy;German;Norway;' Country_list After, I created a external table. It must be stored as textfile: CREATE EXTERNAL TABLE IF NOT EXISTS t_transpose ( field_transpose string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ";" STORED AS TEXTFILE; Then insert on this table like this: insert overwrite t_transpose select REGEXP_REPLACE(Country_list, ';', concat("|", cont, '\n' ) ) as transpose from t_1; After you can select like in my example before:   select split_part(field_transpose,"|",1), split_part(field_transpose,"|",2) from t_transpose; Ps: The final result could have some blank lines, just filter/ignore it. I also put one more ";" in the line comparing with the example informed. ... View more