如何以二进制形式获得SQL连接输出
如何以二进制形式获得SQL连接输出
提问于 2017-03-02 09:42:21
我有两张桌子：
患者访问表
Visit ID Patient ID Date Disease ID
101 1 22-Feb 11
102 5 5-Apr 22
103 3 2-Jul 77
104 2 4-Feb 55
105 6 5-Jan 99
106 2 6-Jan 66
107 2 8-Jan 77
108 7 9-Jan 44
109 5 22-Jan 88
110 1 23-Jan 33第二个表是，
疾病表
Disease ID Disease Name
11 Asthama
22 TB
33 Flu
44 AIDS
55 Cancer
66 Heart Disease
77 ABC
88 XYZ
99 MNO我希望输出如下所示:表中的患者ID为Row，疾病为columns，二进制值表示哪个患者患有哪种疾病。
我应该使用什么查询？
回答 2
Stack Overflow用户
发布于 2017-03-02 10:21:37
如果您使用的是SQL Server，请尝试此方法，希望这能对您有所帮助。使用Case Expression
select t1.patient_id,
case when t2.disease_name='Asthma' then 1 else 0 end as Asthma,
case when t2.disease_name='TB' then 1 else 0 end as TB,
case when t2.disease_name='Flu' then 1 else 0 end as Flu,
case when t2.disease_name='AIDS' then 1 else 0 end as AIDS,
case when t2.disease_name='Cancer' then 1 else 0 end as Cancer,
case when t2.disease_name='Heart Disease' then 1 else 0 end as 'Heart Disease',
case when t2.disease_name='ABC' then 1 else 0 end as ABC,
case when t2.disease_name='XYZ' then 1 else 0 end as XYZ,
case when t2.disease_name='MNO' then 1 else 0 end as MNO
from #table1 t1
left join #table2 t2
on t1.Disease_id=t2.Disease_id
order by t1.patient_idStack Overflow用户
发布于 2017-03-02 12:34:05
尝尝这个
SELECT PatientID, [Asthama],[TB],[Flu],[AIDS],[Cancer],[Heart Disease], [ABC],[XYZ],[MNO]
FROM
(SELECT P.PatientID,D.Disease from Patient P inner join Disease D on P.DiseaseID=D.DiseaseID) AS SourceTable
PIVOT
(
count(Disease)
FOR Disease IN ([Asthama],[TB],[Flu],[AIDS],[Cancer],[Heart Disease],[ABC],[XYZ],[MNO])
) AS PivotTable;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42545368
复制相关文章
相似问题
腾讯云开发者
