300 300 - 1 year ago 48
SQL Question

How to run SQL query to pull data from one of the three columns?

I have a database table "table_name1" in SQL Server 2012 and I am using SQL Server Management Studio version 10.

Data in table looks like:

colpkey col1 col2 col3
1234 AB1234 AB1234
1265 BS5379 BS5379 BS5379
2323 WE7865 WE7865 WE7865
3267 WB7690 WB7690
6543 NULL DH6583
8798 NULL
9403 BF6543 NULL
9856 BH7548 BH7548 BH7548

The query I used to create this table sample table:

create table table_name1 (
colpkey bigint primary key,
col1 varchar(10) ,
col2 varchar(10),
col3 varchar(10)

I want to write a query so it should pull two columns, "colpkey" and "col". The "col" should have value from "col1". If "col1" is NULL or blank then it should get value from "col2". If "col2" is NULL or blank then it should get value from "col3". And if all "col1", "col2" and "col3" are either blank or NULL then value put in "col" should be blank ('').

So for the given sample "table_name1" the query should pull data like:

colpkey col
1234 AB1234
1265 BS5379
2323 WE7865
3267 WB7690
6543 DH6583
9403 BF6543
9856 BH7548

How can I write query to do this? I was trying to use CASE and came up with following query:

select colpkey,
Case WHEN (col1 != null and col1!= '') then col1
(CASE WHEN (col2 != null and col2!= '') then col2
(CASE WHEN (col3 != null and col3!= '') then col3
ELSE '' END) END) END as col
from table_name1;

But it shows blank for each row, as:

colpkey col

Please suggest where I am going wrong with the query?

Answer Source

I like to use the standard SQL coalesce function over SQL Server's proprietary isnull for finding non-null values, and we can also use nullif to satisfy the "blank" requirement:

select colpkey, coalesce(nullif(col1, ''), nullif(col2, ''), nullif(col3, ''), '') as col
from table_name1;