CODEWITHSUNDEEP
hive
Nancy Jean
Nancy Jean

Reputation: 59

how to merge 2 columns in hive?

My output for certain query is

1 null

2 null

null 3

null 4

Where as my output should be

1 3

2 4

How can I make it happen?

Any help would be great!

Upvotes: 1

Views: 2491

Answers (1)

Mukesh S
Mukesh S

Reputation: 2886

So what I understand, this is your data set:

1   10      NULL
1   NULL    11
2   20      NULL
2   NULL    25
11  110     NULL
11  NULL    111
12  120     NULL
12  NULL    125

And this is your output set:

1   10  11
2   20  25
11  110 111
12  120 125

Here's the query that will help to give the required output:

select in.id,in.in_time,out.out_time from(
select id, min(in_time) as in_time from time_table 
  where in_time is not null group by id) in 
join ( 
select id, max(out_time) as out_time from time_table where out_time is not null group by id) out on (in.id = out.id)

I am doing a self join in the table and getting the required columns.

Hope it helps...!!!

Upvotes: 1

Related Questions