CODEWITHSUNDEEP
sqlvariablesselectmultiple-columns
owwoow14
owwoow14

Reputation: 1754

Create column with values from multiple columns in SQL

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

Upvotes: 2

Views: 7360

Answers (2)

Cid
Cid

Reputation: 15247

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

Upvotes: 2

fubar
fubar

Reputation: 17388

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

Upvotes: 2

Related Questions