sqlhiveimpala

impala transpose column to row

How to transpose column data to row data in impala I have tried some solution that not work in impala but working in hive.

Table name : test
Data:
day         name     jobdone
2017-03-25  x_user   5
2017-03-25  y_user   10
2017-03-31  x_user   20
2017-03-31  y_user   1

I want the data should be like that in impala no in hive

Required Output Data
 Day           x_user     y_user
 2017-03-05    5          10
 2001-03-31    20         1

I am able to do in Hive using the Map and collect_list. How can i do in Impala.

Solution

  • Using case + min() or max() aggregation:

    select day,
       max(case when name='x_user' then jobdone end) x_user,
       max(case when name='y_user' then jobdone end) y_user
  from test
  group by day;

    Use sum() instead of max() if there are many records per user, day and you need to sum them.