CODEWITHSUNDEEP
hiveapache-pighiveql
Sarita Singh
Sarita Singh

Reputation: 11

Hive/Pig - Column to Row Conversion

Input Data

**Col1| Col2 | Col3 | Col4**

 2  | 500 | 50 | 100

How to convert columns to rows in hive or pig ?

Expected Output:

**Column  | Value**

Col1  |   2

Col2  | 500

Col3  | 50

Col4  | 100

Upvotes: 1

Views: 74

Answers (2)

Mahesh Richhariya
Mahesh Richhariya

Reputation: 11

SELECT col1 ,col2 FROM ( SELECT MAP('Col1', Col1, 'Col2', Col2, 'Col3', Col3, 'Col4', Col1) AS tmp FROM mytable ) mytab LATERAL VIEW EXPLODE(mytab.tmp) explode_tmp AS col1 ,col2

Upvotes: 1

sandeep rawat
sandeep rawat

Reputation: 4957

You are looking for UNPIVOT.

SELECT 'Col1' as 'Column' ,Col1  AS Value FROM htable
UNION ALL
SELECT 'Col2' as 'Column' ,Col3  AS Value FROM htable
UNION ALL
SELECT 'Col3' as 'Column' ,Col3  AS Value FROM htable
UNION ALL
SELECT 'Col4' as 'Column' ,Col4  AS Value FROM htable

Upvotes: 1

Related Questions