@Hafiz I am able to achieve it with posexplode, please find my solution below. create table comm_article(id int, key string, itemno string, value string);
insert into comm_article values(1, 'A', '1|2|3', '10|20|30'), (1, 'B', '1|2|3', '40|50|60'), (1, 'C', '1|2|3', '70|80|90');
select * from comm_article;
+------------------+-------------------+----------------------+---------------------+
| comm_article.id | comm_article.key | comm_article.itemno | comm_article.value |
+------------------+-------------------+----------------------+---------------------+
| 1 | A | 1|2|3 | 10|20|30 |
| 1 | B | 1|2|3 | 40|50|60 |
| 1 | C | 1|2|3 | 70|80|90 |
+------------------+-------------------+----------------------+---------------------+
with t as (select id, key, split(itemno, '[|]') as itemno_arr, split(value, '[|]') as value_arr from comm_article)
select id, key, itemno, value from t
lateral view posexplode(itemno_arr) myTable1 AS myCol1, itemno
lateral view posexplode(value_arr) myTable2 AS myCol2, value
where myCol1 = myCol2;
+-----+------+---------+--------+
| id | key | itemno | value |
+-----+------+---------+--------+
| 1 | A | 1 | 10 |
| 1 | A | 2 | 20 |
| 1 | A | 3 | 30 |
| 1 | B | 1 | 40 |
| 1 | B | 2 | 50 |
| 1 | B | 3 | 60 |
| 1 | C | 1 | 70 |
| 1 | C | 2 | 80 |
| 1 | C | 3 | 90 |
+-----+------+---------+--------+ If this helps to address your ask, please accept the solution.
... View more
