0

My current table is as below

username   col1 col2 col3
  x         1    2    3
  y         4    5    6
  z         7    8    9

I have several table like this but the number of columns may be different. (example: another table have columns username,col1,col2,col3,col4,col5)

I want to collapse all the columns to single column and introduce the new column to store column names

So now my new tables look like this

username   col_new  val
  x          col1    1
  x          col1    2
  x          col1    3
  y          col2    4
  y          col2    5
  y          col2    6
  z          col3    7
  z          col3    8
  z          col3    9

I can do it manually. But since the number columns are different in each table, So want a hive query which can take variable number of columns and create the above table like format.

Please suggest.

Surjya Narayana Padhi
  • 7,741
  • 25
  • 81
  • 130
  • Possible duplicate of [Hive - Unpivot functionality in hive](http://stackoverflow.com/questions/32621970/hive-unpivot-functionality-in-hive) – o-90 Aug 17 '16 at 18:21
  • See this [Quora](https://www.quora.com/Is-there-a-way-to-transpose-data-in-Hive) question. – o-90 Aug 17 '16 at 18:22
  • @GoBrewers14 The first stack exchange provided seems similar but its not. The quora link is very much relevant. But the solution provided there gives half the solution. It transpose but if the number of columns is dynamic how to transpose it pro grammatically ? – Surjya Narayana Padhi Aug 18 '16 at 02:19
  • I think basically you are describing a [PIVOT( ) function](https://issues.apache.org/jira/browse/HIVE-3776). I definitely don't have the Java acumen to code this myself (nor have I ever come across a use case to justify spending time to write it). There is a great library of UDFs [here](https://github.com/klout/brickhouse), you could open an issue and request this `PIVOT( )` feature. – o-90 Aug 18 '16 at 13:59

0 Answers0