How to combine two columns on the same table using Hive

Question

Right now I have:

Scorecard	team1	team2	Winner	Margin	Ground	Match Date	Year
ODI # 1	Australia	England	Australia	5 wickets	Melbourne	5-Jan-71	1971
ODI # 2	England	Australia	England	6 wickets	Manchester	24-Aug-72	1972
ODI # 3	England	Australia	Australia	5 wickets	Lord's	26-Aug-72	1972
ODI # 4	England	Australia	England	2 wickets	Birmingham	28-Aug-72	1972
ODI # 5	New Zealand	Pakistan	New Zealand	22 runs	Christchurch	11-Feb-73	1973

And what I want to is combine team1 and team2 and then get distant list

Example based on what I have above:

I am using Cloudera Hive- I was trying to get a union to work.

I also tried:

SELECT concat_ws('^',(SPLIT('${team1,team2}',',')));

However, the output is just giving me: ${team1^team2}

eshirvana · Accepted Answer · 2021-11-16T04:36:42.020

0

easiet way would be to use union:

select team1 as teams from tablename
union distinct
select team2 from tablename

Here is another ways using sub query :

Select distinct teams from (
select team1 as teams from tablename
union
select team2 from tablename 
) t

edited Nov 16 '21 at 04:36

answered Nov 16 '21 at 03:28

eshirvana

So I had to add some things to make it work `select distinct team1 as teams from tablename union all select distinct team2 as teams from tablename;` Now, my follow up question how be how to get the new teams list to be distinct? Its not save within the original table. – Jackie S. Nov 16 '21 at 03:41
@Jackis. Hmm , see updated answer , see if that works – eshirvana Nov 16 '21 at 04:37

1 Answers1