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:

teams
Australia
England
New Zealand
Pakistan

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}

Answer 1

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