How to merge two columns from same table into one column using sql

Question

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

shawnt00 · Accepted Answer · 2020-07-08 23:38:27Z

2

It appears that you never have a non-null in both columns so I believe you just want to coalesce the two columns together:

select distinct
    ONES, TWOS, TYPE,
    coalesce(THREES, FOURS) as BOTH,
    FIRST, LAST, LEG_1, LEG_2, DATE
from my_Table;

answered Jul 8, 2020 at 23:38

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Schwern · Accepted Answer · 2020-07-08 23:14:53Z

1

Use coalesce to pick the first non-null value.

select distinct
  ONES, TWOS, TYPE,
  coalesce(THREES, FOURS) as BOTH,
  FIRST, LAST, LEG_1, LEG_2, DATE
from my_Table;

answered Jul 8, 2020 at 23:14

Schwern

167k28 gold badges225 silver badges370 bronze badges

Comments

Hana S · Accepted Answer · 2020-07-08 23:15:10Z

0

you could make the last statement to be distinct,

select distinct * FROM()

answered Jul 8, 2020 at 23:15

Hana S

735 bronze badges

Collectives™ on Stack Overflow

How to merge two columns from same table into one column using sql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related