Hive – Unpivot functionality in hive

by

Whenever I want to pivot a table in Hive, I collect key:value pairs to a map and then reference each key in the next level, creating new columns. This is the opposite of that.

Query:

select a.userid, y.new_id
from (
  select new_id, fruit_name, fruit_code
  from (
    select new_id, map("apple_id", apple_id
                     , "mango_id", mango_id
                     , "grape_id", grape_id
                     , "peach_id", peach_id) as fruit_map
    from table_2 ) x
  lateral view explode(fruit_map) exptbl1 as fruit_name, fruit_code ) y
join table_A a
on (y.fruit_code=a.property_id)

Output:

0001    N456098
0031    N002345
0008    N129087
00013   N109876

More Related Contents:

  1. SQL Server : Columns to Rows
  2. Unpivot with column name
  3. SQL transpose full table
  4. How to simulate UNPIVOT in Access?
  5. HIVE select count(*) non null returns higher value than select count(*)
  6. Hive query performance for high cardinality field
  7. How to unpivot in BigQuery?
  8. unpivot with dynamic columns plus column names
  9. Find last day of a month in Hive
  10. Postgres: convert single row to multiple rows (unpivot)
  11. SQL Unpivot multiple columns Data
  12. Select distinct record from table and perform Sum of column (Pallats, Gross) of a duplicate row. And show that duplicate rows once [closed]
  13. SQL query for multiple rows in single column [closed]
  14. Dynamic Pivot in Oracle’s SQL
  15. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  16. FULL OUTER JOIN with SQLite
  17. How to include “zero” / “0” results in COUNT aggregate?
  18. mySQL :: insert into table, data from another table?
  19. Return 0 if field is null in MySQL
  20. MySQL “between” clause not inclusive?
  21. What’s the best way to capitalise the first letter of each word in a string in SQL Server
  22. How do SQL EXISTS statements work?
  23. postgresql: INSERT INTO … (SELECT * …)
  24. Does Sql JOIN order affect performance?
  25. Best equivalent for IsInteger in SQL Server
  26. Using tuples in SQL “IN” clause
  27. Unfindable SQLite Syntax Error
  28. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  29. IIS connecting to LocalDB
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment