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;
如果您使用的是 SQL Server,则需要输入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;
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
一些数据库会支持这样的东西(Oracle、MySQL、PostgreSQL、SQLite)。
如果您使用的是 SQL Server,则需要输入
substring
.如果您的 facility_Type 是固定长度,那么一种相当静态(非动态)的方法就是识别位置 1、3、5、7 和 9 处的字母。
您没有提及您使用的数据库,但对于SQL Server(例如),可以这样做:
但是,如前所述,这是一种相当麻烦的做事方式。
因此,如果某些东西需要运行“更好”并且更具动态性(例如,如果使用更多选项扩展列表),我会建议找到一种将 y,n,n,y,n 拆分为列的方法。
同样 - 这取决于您使用的数据库系统,因此很难在没有更多信息的情况下专门为您提供建议。