- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
How to merge two rows having same values into single row in HIVE?
- Labels:
-
Apache Hive
Created 11-03-2017 10:31 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am having a table called 'test_analysis' in that the values will be like following,
ID | NAME | DOB | LOCATION |
1 | bob | 08/10/1985 | NEW JERSEY |
1 | bob | 08/10/1985 | NEW YORK |
1 | bob | 08/10/1985 | NORTH CAROLINA |
2 | John | 26/11/1990 | OKLAHOMA |
i want output like,
ID | NAME | DOB | LOCATION |
1 | bob | 08/1/1985 | NEW JERSEY,NEW YORK,NORTH CAROLINA |
2 | John | 26/11/1990 | OKLAHOMA |
Please help me to form a hive query to get expected output.
Created 11-03-2017 11:19 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi. collect_list should be able to achieve what you want:
select id ,name ,dob ,concat_ws(',',collect_list(location)) as location from test_analysis group by id ,name ,dob
I found the solution here: https://stackoverflow.com/questions/44770037/hive-pivot-and-sum
Output should look like this:
1 Bob 08/10/1985 New Jersey,New York,North Carolina 2 John 26/11/1990 Oklahoma
Hope this helps.
Created 04-11-2018 09:35 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Any solution for the below scenario
I have a scenario like below. can anyone please provide some info.
I have the below table. i tried doing this using join with lag and lead function but its not giving the expected result. I'm just not getting any clue how to implement the solution for this.
name date amount
abc 04/06/2018 100
abc 04/06/2018 200
abc 04/13/2018 300
now the output i needed is below.
name date amount
abc 04/06/2018 100
abc 04/06/2018 200
abc 04/13/2018 100
abc 04/13/2018 200
abc 04/13/2018 300
so here 300 is the new value and 100,200 from mon will also shown for tue. Do we have any way to do this in hive. Any help will be greatly appreciated.
Created 11-03-2017 07:42 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The same scenario is solved here:
Hope this helps.
Created 04-12-2018 07:11 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @johny gate
Below query works but its kind of dirty. Hope it Helps!!
select * from a left join (select*,lag(col3)over (partition by col1 order by col2) as lag_val from a) tblb on tbl b.col1=a.col1 and a.col2=tblb.lag_val
Created 11-30-2018 02:17 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello folks,
Please help me with the following query:
There are two tables T1 and T2 find the sum of price if customer buys all the product how much he has to pay after discount.
Table : T1
================================
ProductID | ProductName | Price
--------------------------------
1 | p1 | 1000
2 | p2 | 2000
3 | p3 | 3000
4 | p4 | 4000
5 | p5 | 5000
Table : T2
=======================
ProductID | Disocunt %
-----------------------
1 | 10
2 | 15
3 | 10
4 | 15
5 | 20
,Hello everyone,
Please help me with the following query in Hive.
There are two tables T1 and T2 find the sum of price if customer buys all the product how much he has to pay after discount.
Table : T1
================================
ProductID | ProductName | Price
--------------------------------
1 | p1 | 1000
2 | p2 | 2000
3 | p3 | 3000
4 | p4 | 4000
5 | p5 | 5000
Table : T2
=======================
ProductID | Disocunt %
-----------------------
1 | 10
2 | 15
3 | 10
4 | 15
5 | 20
