help required on sql server query

I have a four column in table1. I want to select just single column value out of four based on below logic.

If column1 is null Then take column2 value.
If   column2 is null  Then take column3 value. if  column3 is null  Then take column4 value
 
Column1           column2       column3       column4
------------------------------------------------------------------
col1value         col2value     col3value    col4value
null                   col2val1      null              null
null                  null               null             col4value1

So result of the query should return following result
col
----------------
col1value
col2val1
col4value1  

Please help.

Thanks  
r_pat72Asked:
Who is Participating?
 
Missus Miss_SellaneusCommented:
SELECT NULLIF(column1,NULLIF(column2,NULLIF(column3,column4))) AS col FROM table1
0
 
r_pat72Author Commented:
it is not giving correct result. Could you please see this again.
0
 
Missus Miss_SellaneusCommented:
Sorry about that. I don't have SQL Server to test with and Access doesn't have the same function :(
Looks to be so straight forward.

Can you tell what it *is* doing?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
ImperdonatoCommented:
Select isnull(Column1, isnull(Column2, isnull(Column3, column4))) from table1Select isnull(Column1, isnull(Column2, isnull(Column3, column4))) from table1


Hope that helps.
0
 
reb73Commented:
SELECT COALESCE(Column1, Column2, Column3, column4) AS 'Col'
0
 
Mark WillsTopic AdvisorCommented:
Just in case you missed reb73's posting - as I almost did  (getting better - did a refresh first then saw it - good huh) the COALESCE function is exactly what you need.

SELECT COALESCE(Column1, Column2, Column3, column4) AS 'Col'

Points Set and Match to reb73 as far as I can tell...
0
 
r_pat72Author Commented:
Thanks for all reply.
0
 
reb73Commented:
Not sure why you'd want to accept an answer  (from Miss Sellaneus) that doesn't do what was requested. Imperdonato atleast provided a comparable solution and could have got some points..
0
 
Mark WillsTopic AdvisorCommented:
Agree with reb73 - sorry about that - but you do need to have the correct information.  Please try the following and see the differences:

create table #t1 (column1 varchar(20),column2 varchar(20), column3 varchar(20), column4 varchar(20))
insert #t1 values ('1',NULL,'3',NULL)
insert #t1 values (NULL,NULL,'3',NULL)
insert #t1 values (NULL,NULL,NULL,'4')
insert #t1 values ('1','2','3','4')

SELECT NULLIF(column1,NULLIF(column2,NULLIF(column3,column4))) AS nullif_col, coalesce(column1,column2,column3,column4) as coalesce_col FROM #t1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.