[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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  
0
r_pat72
Asked:
r_pat72
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now