How to show records in a Column which are existing in various columns in a table

Question

A table has 3 columns A,B and C. I want to get these records in a Column D.

Table structure

A | B | C
------------
X | Null | Null
--------------------
Null | Y | Null
--------------------
Null | Null | Z

And output should be

D
---
X
Y
Z

Answer 1

Thanks for letting us know. The proper etiquette is to select the answer that best fits by clicking the check mark next to the accepted answer.

Answer 2

I tested this in MySQL:

select a, b, c, concat(COALESCE(a, ''), COALESCE(b, ''), COALESCE(c, '')) from t;
+------+------+------+-----------------------------------------------------------+
| a | b | c | concat(COALESCE(a, ''), COALESCE(b, ''), COALESCE(c, '')) |
+------+------+------+-----------------------------------------------------------+
| x | NULL | NULL | x |
| NULL | y | NULL | y |
| NULL | NULL | z | z |
+------+------+------+-----------------------------------------------------------+
3 rows in set (0.01 sec)

The COALESCE function helps convert null to empty string there.
concat will return null if any of its parameters are null. COALESCE makes sure none of the actual parameters to concat are null.

COALESCE is supported in both MySQL and Microsoft SQL Server but I didn't check other vendors.

Answer 3

Josh Greig You can actually simplify your approach as follows:

SELECT COALESCE(a, b, c, "z") as D
FROM coalesce_demo;

This query will use the first non null value available.

To take this demo further, I specify a default value to be "z" when all other columns are NULL.

Check out the SQL Fiddle I created to demonstrate this:

http://sqlfiddle.com/#!9/6554a0/2/0

This returns a single column result set:

D
---
a
b
z
c
...
...
...

Answer 4

Durgesh Verma Were you able to get your query to work?

Answer 5

How do you want to combine those values into one column? Are they strings that can be concatenated together?

If yes, you could do something like this:
select a, b, c, concat(a, b, c) from mytable

Answer 6

Josh Greig I have updated my question. Please take a look and help me out of this😊

Answer 7

Yes Thanks