1

I want to tanspose rows and columns in a hive table, get all rows and create columns for them , while the columns will become the rows.

My data look like this

Name    week1   week2   week3
John    5        6       3
Mary    4        2       5
Marc    7        8       9 
Jane    1        2       8

and the final output should be like this

week    John    Mary    Marc    Jane
week1   5        4        7       1
week2   6        2        8       2
week3   3        5        9       8

I have tried using a code from an existing thread but the rows that will become columns are so many (not just one like the 'val' column in that query) that I need to find a dynamic way of turning them into columns (and not mentioning the values one by one in the query)

SELECT id
  , bool
  , val
FROM (
  SELECT id
    , MAP('yes', yes, 'no', no) AS tmp_column
  FROM database.table ) x
LATERAL VIEW EXPLODE(tmp_column) exptbl AS bool, val

the new column val that this query creates will not be suffice fo rmy data cause i am going to have a lot of columns

Aser16
  • 61
  • 1
  • 4
  • Hi, there may be a way: to dynamically generate the statement, if you have access to the Hive Metastore. Using the metastore tables, you can generate the statement and then run it. – F.Lazarescu Jul 16 '19 at 07:22
  • Hey , unfortunately i don't have access to the metastore tables. But i could potentially try do the exact same thing in oracle sql , if it makes any difference. – Aser16 Jul 16 '19 at 08:12
  • So, probably you can't do it dynamically. There are similar posts: [here](https://stackoverflow.com/questions/37436710/is-there-a-way-to-transpose-data-in-hive) or [here](https://stackoverflow.com/questions/23025380/how-to-transpose-pivot-data-in-hive) – F.Lazarescu Jul 16 '19 at 08:19
  • 1
    Thanks a lot. I will try to work with python. i htink it might be easier. – Aser16 Jul 17 '19 at 06:02

0 Answers0