title: SQL conditionally return results from one of two DB columns into single result column
body: <p>Hello I have the below SQL query that I would like to conditionally return results from one of two DB columns (DEA and DEA_ALT) into one result column depending which of those two columns is valued (not null). The DB is setup where there are two fields that can store a field called DEA# however both fields have the same purpose, and therefore I would like to return the results from either column depending on which one is valued (can be either).</p>
  
  <pre><code>SELECT Distinct t1.UserName,
              t1.[LoginID],
              t7.FirstName, 
              t7.LastName, 
              t7.Title ,
              t7.EndDateOfValidity,      
              NPI = ISNULL(HStaffIdentifiers.Value,''),
              Type= HStaffIdentifiers.Type, 
              DEA=ISNULL(t5.PrescriberCode,''),
              --
              --
              DEA_ALT = ISNULL((select CASE ISNULL(PC.PrescriberCode,'')
                            when '' then ISNULL(HS.Value,'')
                            end
                            from HStaff PC
                            left outer join HStaffIdentifiers HS
                            ON PC.ObjectID = HS.Staff_oid
                            where HS.Type = 'DEA'
                            and HS.Staff_oid = HStaffIdentifiers.Staff_oid
                            ),ISNULL(t5.PrescriberCode,'')),
              --
              --
              Speciality = ISNULL(HDataDictionaryItem3.Description,''),
              Preference_Group = HDataDictionaryItem1.Name
          FROM HSUser t1  with (nolock)
               Left Outer JOIN HStaff t5  with (nolock) 
               ON t1.UserName=t5.MSINumber
               LEFT OUTER JOIN HStaffIdentifiers HStaffIdentifiers WITH (NOLOCK)
               ON t5.ObjectID = HStaffIdentifiers.Staff_oid
               AND HStaffIdentifiers.Type = 'NPI'
               JOIN HPerson t6  with (nolock) 
               ON t5.ObjectID=t6.ObjectID
               JOIN HName t7  with (nolock) 
               ON t6.ObjectID=t7.Person_oid   
               and (t7.EndDateOfValidity &lt; '2010-01-01 00:00:00.000'
                  or  t7.EndDateOfValidity is null)
  
               JOIN HDataDictionaryItem HDataDictionaryItem1 WITH (NOLOCK)
               ON t1.PreferenceGroup_oid = HDataDictionaryItem1.ObjectID
                   AND HDataDictionaryItem1.ItemType_oid = 98
  
  left outer join HDataDictionaryItem HDataDictionaryItem3 WITH (NOLOCK)
               ON t5.PrimarySpeciality_oid = HDataDictionaryItem3.ObjectID
               AND HDataDictionaryItem3.ItemType_oid = 43
  
               WHERE 
  
                t1.EndTime &gt;= GETDATE()
               AND t1.BeginTime&lt;=GETDATE()  
               AND t5.Active=1
               AND t6.IsDeleted=0
               AND t5.StaffType = 0 
  order by  t7.LastName,  t7.FirstName
  </code></pre>
  
  <p>Snippet from the results at the following link:
  <a href="http://s7.postimg.org/i5xlsjhnv/12_31_2014_10_00_13_AM.jpg" rel="nofollow">http://s7.postimg.org/i5xlsjhnv/12_31_2014_10_00_13_AM.jpg</a></p>
  
  <p>As you can see from the query results (highlighted in red) there are cases when the DEA may only be populated in one of those two columns, however I want the results to be returned to a single column from the DEA column if it is valued, If not, then I want to check to see if DEA_ALT is valued and if so return the result to this single column. How can I accomplish this?</p>