How to retrieve 'y' and 'n' comma separated values as 'yes' or 'no' from a single column

Question

I have a table in a database with these columns

id     name    facility_Type
1      Taluka    y,n,n,y,n

Now I want to retrieve the comma separated values like this

id      name     Ambulance   ward  male_doctors  female_doctors  operation_theator
 1    Taluka      yes         no      no             yes              no          

Answer 1

Some databases would support something like this (Oracle, MySQL, PostgreSQL, SQLite).

select T.id,
       T.name,
       case substr(T.facility_type, 1, 1) when 'y' then 'yes' else 'no' end as Ambulance,
       case substr(T.facility_type, 3, 1) when 'y' then 'yes' else 'no' end as ward,
       case substr(T.facility_type, 5, 1) when 'y' then 'yes' else 'no' end as male_doctors,
       case substr(T.facility_type, 7, 1) when 'y' then 'yes' else 'no' end as female_doctors,
       case substr(T.facility_type, 9, 1) when 'y' then 'yes' else 'no' end as operation_theator
from T;

If you are on SQL Server you need to type out substring.

select T.id,
       T.name,
       case substring(T.facility_type, 1, 1) when 'y' then 'yes' else 'no' end as Ambulance,
       case substring(T.facility_type, 3, 1) when 'y' then 'yes' else 'no' end as ward,
       case substring(T.facility_type, 5, 1) when 'y' then 'yes' else 'no' end as male_doctors,
       case substring(T.facility_type, 7, 1) when 'y' then 'yes' else 'no' end as female_doctors,
       case substring(T.facility_type, 9, 1) when 'y' then 'yes' else 'no' end as operation_theator
from T;

Answer 2

If your facility_Type is a fixed length, and then a rather static (non-dynamic) method of doing it would just be to identify the letter at position 1, 3, 5, 7 and 9.

You do not mention what database you use, but for SQL Server (for example) it could be done something like this:

SELECT
id, name,
CASE
    WHEN SUBSTRING(facility_type, 1, 1) = 'y' THEN 'Yes'
    WHEN SUBSTRING(facility_type, 1, 1) = 'n' THEN 'No'
    ELSE NULL 
END AS Ambulance,
CASE
    WHEN SUBSTRING(facility_type, 3, 1) = 'y' THEN 'Yes'
    WHEN SUBSTRING(facility_type, 3, 1) = 'n' THEN 'No'
    ELSE NULL 
END AS ward,
CASE
    WHEN SUBSTRING(facility_type, 5, 1) = 'y' THEN 'Yes'
    WHEN SUBSTRING(facility_type, 5, 1) = 'n' THEN 'No'
    ELSE NULL 
END AS male_doctors ,
CASE
    WHEN SUBSTRING(facility_type, 7, 1) = 'y' THEN 'Yes'
    WHEN SUBSTRING(facility_type, 7, 1) = 'n' THEN 'No'
    ELSE NULL 
END AS female_doctors,
CASE
    WHEN SUBSTRING(facility_type, 9, 1) = 'y' THEN 'Yes'
    WHEN SUBSTRING(facility_type, 9, 1) = 'n' THEN 'No'
    ELSE NULL 
END AS operation_theator
from CTE

However, as mentioned it is a rather cumbersome way of doing things.

So if something which needs to run "better" and be more dynamic (for example if expanding the list with more options) I would recommend, finding a method of splitting the y,n,n,y,n into columns.

Again - this vary depending on which database system you use, so it's difficult to advice you on that specifically without more information.