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}
CodePudding user response：
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