UN-Pivot in HIVE

Asked May 02 '18 at 17:17

Active May 02 '18 at 17:17

Viewed 60 times

I am working with a HIVE table and have a data like below:

id  channel pkg_1_sk    pkg_1_nm    pkg_1_prod_1_sk pkg_1_prod_1_nm pkg_1_prod_2_sk pkg_1_prod_2_nm pkg_2_sk    pkg_2_nm    pkg_2_prod_1_sk pkg_2_prod_1_nm pkg_2_prod_2_sk pkg_2_prod_2_nm
abc XYZ      1          Package-1   1               Prod-1          2               Prod-2           2          Package-2   3                 Prod-3         2               Prod-2

and I want the data like below:

id  channel pkg_sk  prod_sk
abc XYZ     1       1
abc XYZ     1       2
abc XYZ     2       3
abc XYZ     2       2

Is there any way I can achieve the result without using UNION ALL. The source table is huge and UNION ALL will cause a performance issue.

asked May 02 '18 at 17:17

Koushik Chandra

1,565
12
37
73

Did you check this **2nd** answer to another similar question https://stackoverflow.com/a/32622625/7998591 – Kaushik Nayak May 03 '18 at 04:27
IMHO If the data were in loadable format, DDL+DML, the chances someone will help are much better – leftjoin May 03 '18 at 12:19

UN-Pivot in HIVE

0 Answers0