Created 11-03-2017 10:31 AM
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
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
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
The same scenario is solved here:
Hope this helps.
Created 04-12-2018 07:11 AM
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
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