Please I need a way to combine 2 columns from the same table. I have a table let's say my_Table. I extracted just few columns that i'm interested in as shown in the table below using the following statement:
select distinct ONES, TWOS, TYPE, THREES, FOURS, FIRST, LAST, LEG_1, LEG_2, DATE from my_Table; Result is as shown:
+-----+-----+----+-------+-------+------+-----+------+------+-----------+ |ONES |TWOS |TYPE|THREES |FOURS |FIRST |LAST |LEG_1 |LEG_2 |DATE | +-----+-----+----+-------+-------+------+-----+------+------+-----------+ |AAAA1|AAAA2|O |AAAA3 |null |null |null |INC |WELL |null | |BBBB1|BBBB2|P |null |BBBB4 |DALE |BETY |null |null |2020-01-01 | |CCCC1|CCCC2|P |null |CCCC4 |KENT |SALA |null |null |2018-10-18 | |DDDD1|DDDD2|P |null |DDDD4 |KYLE |GIRE |null |null |2020-04-24 | +-----+-----+----+-------+-------+------+-----+------+------+-----------+ I perform a UNION operation in order to combine columns "THREES" and "FOURS" into column "BOTH" as shown in the select statement below,
select * from (select distinct ONES, TWOS, TYPE, THREES as BOTH, FIRST, LAST, LEG_1, LEG_2, DATE from my_Table) UNION (select distinct ONES, TWOS, TYPE, FOURS as BOTH, FIRST, LAST, LEG_1, LEG_2, DATE from my_Table) Got the following result
+-----+-----+----+-----+-----+-----+------+-----+-----------+ |ONES |TWOS |TYPE|BOTH |FIRST|LAST |LEG_1 |LEG_2|DATE | +-----+-----+----+-----+-----+-----+------+-----+-----------+ |CCCC1|CCCC2|P |null |KENT |SALA |null |null |2018-10-18 | |AAAA1|AAAA2|O |null |null |null |INC |WELL |null | |AAAA1|AAAA2|O |AAAA3|null |null |INC |WELL |null | |BBBB1|BBBB2|P |null |DALE |BETY |null |null |2020-01-01 | |DDDD1|DDDD2|P |DDDD4|KYLE |GIRE |null |null |2020-04-24 | |DDDD1|DDDD2|P |null |KYLE |GIRE |null |null |2020-04-24 | |CCCC1|CCCC2|P |CCCC4|KENT |SALA |null |null |2018-10-18 | |BBBB1|BBBB2|P |BBBB4|DALE |BETY |null |null |2020-01-01 | +-----+-----+--- +-----+-----+-----+------+-----+-----------+ How can I perform the operation such that my final result will be as shown below
+-----+-----+----+-----+-----+-----+------+-----+-----------+ |ONES |TWOS |TYPE|BOTH |FIRST|LAST |LEG_1 |LEG_2|DATE | +-----+-----+----+-----+-----+-----+------+-----+-----------+ |AAAA1|AAAA2|O |AAAA3|null |null |INC |WELL |null | |DDDD1|DDDD2|P |DDDD4|KYLE |GIRE |null |null |2020-04-24 | |CCCC1|CCCC2|P |CCCC4|KENT |SALA |null |null |2018-10-18 | |BBBB1|BBBB2|P |BBBB4|DALE |BETY |null |null |2020-01-01 | +-----+-----+--- +-----+-----+-----+------+-----+-----------+ Thank you
