How to create/add a column as a return output in an SQL select query based on another column's values?

Question

I have dynamically added a column (industry_type) to a SQL view using Case statement. Now I would like to use that same column name/values to add another dynamic column with industry_group output. I tried using the same method to create industry_group column. But I am unable to do so because the column created in view is not available in "main table".

Do I need to create a new column with industry_group at the source? Or is there a way to create that dynamically within the same view and return industry_group values?

Would be a great help if you could assist me with this problem.

Sample 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

Output:

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

Tried this script1:

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

Output:

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

Tried script 2

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

Unfortunately "Script 2" isn't working.

Desired output:

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

I have more than 10 search criteria against which I need to return the correct industry group

Please note: industry_type is not available in the "master table"

Thanks, everyone!

Answer 1

Your output doesn't seem to match the query: the query doesn't show any column date, I would expect an output with 3 columns:

Column1, column2, industry_type

Instead it has only 2

It would help if you paste the actual code instead of pictures from excel

Anyway to add a column in a select just add it among the fields:

SELECT column1,
column2,
case
...
END AS industry_type,
"PF" as industry_group
FROM table;

---

Update after question enhancement

Each column is a rule per se, if you want to make it depending on a previous one you should rewrite the rule twice, or making a nested query

First example

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],
    case
       when (column 1 like '%12321%' or column 2 like'%poultry%' 
          then 'PF'
       when (column 1  like '%43434%') or column 2 like'%Hospi%' 
          then 'HOS'
       else null
    end as [industry_group],
from table

second example

Select
Date, industry_type,
Case
When industry_type = "Hospitals" then "HOS"
When industry_type = "Poultry facilities" then "POL"
...
End
As industry_group
FROM (
    select 
    date,
    case
       when (column 1 like '%12321%' or column 2 like'%poultry%' 
          then 'Poultry Facilities'
    end as [industry_type],
    case
       when (column 1  like '%43434%') or column 2 like'%Hospi%' 
          then 'Hospitals'
          else null
    end as [industry_type],
    from table)