sqlhiveclouderacloudera-quickstart-vm

How to combine two columns on the same table using Hive

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}

Solution

  • 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