Create column with values from multiple columns in SQL

Question

I want to add a column to a table that includes value from one of two columns, depending on which row contains the value.

For instance,

SELECT
  concat("Selecting Col1 or 2", cast("Col1" OR "Col2" as string)) AS relevantinfo,
FROM table

I do not know much SQL and I know this query does not work. Is this even possible to do?

Col1    Col2
1       
         4
3
4
         5

FINAL RESULT

Col1    Col2    relevantinfo
1                1
        4        4
3                3
4                4
        5        5

Please, share sample data with an output which you want. as per your current need you can Check coalesce function w3schools.com/sql/func_sqlserver_coalesce.asp — Rajat Jaiswal
– Rajat Jaiswal, Commented May 17, 2018 at 8:31
Could you have a value in both Col1 and Col2 for the same entry? — Cid
– Cid, Commented May 17, 2018 at 8:47

fubar · Accepted Answer · 2018-05-17 08:54:30Z

2

You can use the COALESCE function, which will return the first non-null value in the list.

SELECT col1, col2, COALESCE(col1, col2) AS col3 
FROM t1;

Working example: http://sqlfiddle.com/#!9/05a83/1

answered May 17, 2018 at 8:54

fubar

17.4k4 gold badges43 silver badges47 bronze badges

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

Comments

Cid · Accepted Answer · 2018-05-17 09:24:16Z

2

I wouldn't alter the table structure to add a redundant information that can be retrieved with a simple query.

I would rather use that kind of query with IFNULL/ISNULL(ColumnName, 'Value to use if the col is null'):

--This will work only if there can't be a value in both column at the same time
--Mysql
SELECT CONCAT(IFNULL(Col1,''),IFNULL(Col2,'')) as relevantinfo FROM Table

--Sql Server
SELECT CONCAT(ISNULL(Col1,''),ISNULL(Col2,'')) as relevantinfo FROM Table

edited May 17, 2018 at 9:24

answered May 17, 2018 at 8:55

Cid

15.3k4 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

Create column with values from multiple columns in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related