title: SQL Case Statement that Evaluates Multiple columns and returns column that matches value criteria?
body: <p>Thanks for the clarifications on the posting. I was trying to keep it simple, but here are some additional details/depth for what I'm trying to accomplish. </p>
  
  <p>Basically I want to search through 3 different fields and identify the "Undergraduate" program first (if one exists).
  If no valid undergraduate program exists, I then want to search for the "Graduate" Program (if one exists).
  If no graduate program exists, then I want to search for the "Doctoral" Program. </p>
  
  <p>I have 6 columns I'm trying to work with. Here's an example:</p>
  
  <pre><code>Program1    Program2    Program3    AcadLevel1  AcadLevel2  AcadLevel3
  DNP.FNP     GR          NULL            DO          GR          NULL
  MS.ACT      BS.BUAN     NULL            GR          UG          NULL
  DNP.FNP     GR          NULL            DO          GR          NULL
  EDD.EDLC    GR          NULL            DO          GR          NULL
  MAED.EDL    EDD.EDLC    GR              GR          DO          GR
  DNP.FNP     GR          UG              DO          GR          UG
  </code></pre>
  
  <p>I want to ignore all NULL values and I want to ignore any "GR", "UG", or "DO" in any of the Program1, 2, and 3 fields as they are not valid programs IDs. They should only be used here to identify the academic level of the program.</p>
  
  <p>The end goal is to identify a single program. If there's an undergraduate program, that needs to be that program. If there's no undergraduate program, then the graduate program must be it. If there's no graduate program, the doctoral program. If there are 2 undergraduate programs, I don't care which one is included. And likewise if there are 2 graduate programs (and no undergraduate programs) either one will suffice.</p>
  
  <p>One of the challenges that I'm facing is that there are many possibilities of how the programs are "laid out" in the three columns...there's no particular order or sequence. </p>
  
  <p>Thanks again for any help/guidance and sorry for the earlier confusion.</p>
  
  ###################### Update
  
  <p>Here's an example for what I'm trying to accomplish:</p>
  
  <pre><code>CASE WHEN ISNULL(Program2,'')='' THEN Program1
         WHEN (AcadLevel1 = 'UG' and Program1 NOT IN ('UG','SP','GR','DO')) THEN Program1
         WHEN (AcadLevel1 = 'UG' and Program1 IN ('UG','SP','GR','DO')) THEN
          CASE WHEN ISNULL(Program2,'')='' THEN Program1 
               WHEN Program2 IN ('UG','SP','GR','DO') AND ISNULL(Program3,'')='' THEN Program2
               WHEN Program2 IN ('UG','SP','GR','DO') AND ISNULL(Program3,'')&lt;&gt;'' THEN Program3
               WHEN Program2 IN ('UG','SP','GR','DO') AND Program3 IN ('UG','SP','GR','DO') THEN Program1
               WHEN Program2 NOT IN ('UG','SP','GR','DO') AND ISNULL(Program3,'')='' THEN Program2
               WHEN Program2 NOT IN ('UG','SP','GR','DO') AND AcadLevel2 = 'UG' THEN Program2
               WHEN Program2 NOT IN ('UG','SP','GR','DO') AND AcadLevel3 = 'UG' AND Program3 NOT IN ('UG','SP','GR','DO') THEN Program3
               ELSE 'Other'
          END
   ELSE 'Other2' 
  END AS 'Program_New'
  </code></pre>
  
  <p>I'm having difficulty analyzing every possible scenario between all of the fields to pull the correct Program value. And while I can keep writing additional CASE statements until I don't have any "other" error codes I still won't necessarily know if they fix all possible scenarios. </p>
  
  <p>I was hoping for a more comprehensive and simple solution, but perhaps there is none...</p>