r_pat72
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Looks to be so straight forward.
Can you tell what it *is* doing?
Select isnull(Column1, isnull(Column2, isnull(Column3, column4))) from table1Select isnull(Column1, isnull(Column2, isnull(Column3, column4))) from table1
Hope that helps.
Hope that helps.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all reply.
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..
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(colu mn2,NULLIF (column3,c olumn4))) AS nullif_col, coalesce(column1,column2,c olumn3,col umn4) as coalesce_col FROM #t1
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(colu
ASKER