title: How to create/add a column as a return output in an SQL select query based on another column's values?
body: <p>I have dynamically added a column (<code>industry_type</code>) to a SQL view using Case statement. Now I would like to use that same column name/values to add another dynamic column with <code>industry_group</code> output. I tried using the same method to create <code>industry_group</code> column. But I am unable to do so because the column created in view is not available in &quot;main table&quot;.</p>
  <p>Do I need to create a new column with <code>industry_group</code> at the source? Or is there a way to create that dynamically within the same view and return <code>industry_group</code> values?</p>
  <p>Would be a great help if you could assist me with this problem.</p>
  <p>Sample Code:</p>
  <pre><code>select 
      column 1, column 2, date,
      case
          when (column 1 like '%12321%' or column 2 like'%poultry%' 
             then 'Poultry Facilities'
          when (column 1  like '%43434%') or column 2 like'%Hospi%' 
             then 'Hospitals'
          else null
      end as [industry_type],
  from table
  </code></pre>
  <p>Output:</p>
  <pre><code>Date        Industry Type
  ------------------------------
  7/02/2021   Poultry facilities
  7/02/2021   Poultry facilities
  7/02/2021   Poultry facilities
  10/02/2021  Poultry facilities
  11/01/2021  Hospitals
  10/01/2021  Hospitals
  5/02/2021   Hospitals
  </code></pre>
  <p>Tried this script1:</p>
  <pre><code>select 
      column 1, column 2, date,
      case
         when (column 1 like '%12321%' or column 2 like'%poultry%' 
            then 'Poultry Facilities'
         when (column 1  like '%43434%') or column 2 like'%Hospi%' 
            then 'Hospitals'
         else null
      end as [industry_type],
      'PF' as industry_group,
  from table
  </code></pre>
  <p>Output:</p>
  <pre><code>Date        Industry Type     Industry Group
  ----------------------------------------------
  7/02/2021   Poultry facilities  PF
  7/02/2021   Poultry facilities  PF
  7/02/2021   Poultry facilities  PF
  10/02/2021  Poultry facilities  PF
  11/01/2021  Hospitals           PF
  10/01/2021  Hospitals           PF
  5/02/2021   Hospitals           PF
  </code></pre>
  <p>Tried script 2</p>
  <pre><code>select 
      column 1, column 2, date,
      case
         when (column 1 like '%12321%' or column 2 like'%poultry%' 
            then 'Poultry Facilities'
      end as [industry_type],
      'PF' as industry_group,
      case
         when (column 1  like '%43434%') or column 2 like'%Hospi%' 
            then 'Hospitals'
            else null
      end as [industry_type],
      'HOS' as industry_group,
  from table
  </code></pre>
  <p>Unfortunately &quot;Script 2&quot; isn't working.</p>
  <p>Desired output:</p>
  <pre><code>Date        Industry Type   Industry Group
  ---------------------------------------
  7/02/2021   Poultry facilities  PF
  7/02/2021   Poultry facilities  PF
  7/02/2021   Poultry facilities  PF
  10/02/2021  Poultry facilities  PF
  11/01/2021  Hospitals          HOS
  10/01/2021  Hospitals          HOS
  5/02/2021   Hospitals          HOS
  </code></pre>
  <p>I have more than 10 search criteria against which I need to return the correct industry group</p>
  <p>Please note: <code>industry_type</code> is not available in the &quot;master table&quot;</p>
  <p>Thanks, everyone!</p>