select t1.subject_id,t1.hadm_id,t1.icd9_code, t2.admittime,t2.dischtime,t2.deathtime,t2.insurance,t2.marital_status, t3.gender,t3.dob,t3.dod,t3.expire_flag, t4.itemid,t4.value,t4.valuenum,t4.valueuom,t4.flag, t5.label,t5.category, t6.startdate,t6.enddate,t6.drug_type,t6.drug,t6.drug_name_poe,t6.drug_name_generic from mimiciii.diagnoses_icd t1 left join mimiciii.admissions t2 on t1.hadm_id=t2.hadm_id left join mimiciii.patients t3 on t1.subject_id=t3.subject_id left join mimiciii.labevents t4 on t1.hadm_id=t4.hadm_id left join mimiciii.d_labitems t5 on t4.itemid=t5.itemid left join mimiciii.prescriptions t6 on t1.hadm_id=t6.hadm_id where t1.icd9_code in('41404','41030','41021','41042','41407') and t4.itemid in('50803','50804','50805','50806','50808','50809','50811','50813','50814','50815', '50816','50817','50818','50820','50821','50822','50824','50852','50853','50854', '50855','50861','50862','50863','50866','50867','50868','50869','50870','50878', '50882','50883','50884','50885','50889','50893','50896','50902','50904','50905', '50906','50907','50908','50909','50910','50911','50912','50915','50927','50930', '50931','50945','50947','50949','50950','50951'')如何将查询到的结果转换为每个病人一行的宽表

可以使用SQL的PIVOT函数将查询到的结果转换为每个病人一行的宽表。

以下是示例代码：

SELECT 
    subject_id,
    hadm_id,
    admittime,
    dischtime,
    deathtime,
    insurance,
    marital_status,
    gender,
    dob,
    dod,
    expire_flag,
    MAX(CASE WHEN icd9_code = '41404' THEN 1 ELSE 0 END) AS diag_41404,
    MAX(CASE WHEN icd9_code = '41030' THEN 1 ELSE 0 END) AS diag_41030,
    MAX(CASE WHEN icd9_code = '41021' THEN 1 ELSE 0 END) AS diag_41021,
    MAX(CASE WHEN icd9_code = '41042' THEN 1 ELSE 0 END) AS diag_41042,
    MAX(CASE WHEN icd9_code = '41407' THEN 1 ELSE 0 END) AS diag_41407,
    MAX(CASE WHEN itemid = '50803' THEN value ELSE NULL END) AS lab_50803,
    MAX(CASE WHEN itemid = '50804' THEN value ELSE NULL END) AS lab_50804,
    MAX(CASE WHEN itemid = '50805' THEN value ELSE NULL END) AS lab_50805,
    MAX(CASE WHEN itemid = '50806' THEN value ELSE NULL END) AS lab_50806,
    MAX(CASE WHEN itemid = '50808' THEN value ELSE NULL END) AS lab_50808,
    MAX(CASE WHEN itemid = '50809' THEN value ELSE NULL END) AS lab_50809,
    MAX(CASE WHEN itemid = '50811' THEN value ELSE NULL END) AS lab_50811,
    MAX(CASE WHEN itemid = '50813' THEN value ELSE NULL END) AS lab_50813,
    MAX(CASE WHEN itemid = '50814' THEN value ELSE NULL END) AS lab_50814,
    MAX(CASE WHEN itemid = '50815' THEN value ELSE NULL END) AS lab_50815,
    MAX(CASE WHEN itemid = '50816' THEN value ELSE NULL END) AS lab_50816,
    MAX(CASE WHEN itemid = '50817' THEN value ELSE NULL END) AS lab_50817,
    MAX(CASE WHEN itemid = '50818' THEN value ELSE NULL END) AS lab_50818,
    MAX(CASE WHEN itemid = '50820' THEN value ELSE NULL END) AS lab_50820,
    MAX(CASE WHEN itemid = '50821' THEN value ELSE NULL END) AS lab_50821,
    MAX(CASE WHEN itemid = '50822' THEN value ELSE NULL END) AS lab_50822,
    MAX(CASE WHEN itemid = '50824' THEN value ELSE NULL END) AS lab_50824,
    MAX(CASE WHEN itemid = '50852' THEN value ELSE NULL END) AS lab_50852,
    MAX(CASE WHEN itemid = '50853' THEN value ELSE NULL END) AS lab_50853,
    MAX(CASE WHEN itemid = '50854' THEN value ELSE NULL END) AS lab_50854,
    MAX(CASE WHEN itemid = '50855' THEN value ELSE NULL END) AS lab_50855,
    MAX(CASE WHEN itemid = '50861' THEN value ELSE NULL END) AS lab_50861,
    MAX(CASE WHEN itemid = '50862' THEN value ELSE NULL END) AS lab_50862,
    MAX(CASE WHEN itemid = '50863' THEN value ELSE NULL END) AS lab_50863,
    MAX(CASE WHEN itemid = '50866' THEN value ELSE NULL END) AS lab_50866,
    MAX(CASE WHEN itemid = '50867' THEN value ELSE NULL END) AS lab_50867,
    MAX(CASE WHEN itemid = '50868' THEN value ELSE NULL END) AS lab_50868,
    MAX(CASE WHEN itemid = '50869' THEN value ELSE NULL END) AS lab_50869,
    MAX(CASE WHEN itemid = '50870' THEN value ELSE NULL END) AS lab_50870,
    MAX(CASE WHEN itemid = '50878' THEN value ELSE NULL END) AS lab_50878,
    MAX(CASE WHEN itemid = '50882' THEN value ELSE NULL END) AS lab_50882,
    MAX(CASE WHEN itemid = '50883' THEN value ELSE NULL END) AS lab_50883,
    MAX(CASE WHEN itemid = '50884' THEN value ELSE NULL END) AS lab_50884,
    MAX(CASE WHEN itemid = '50885' THEN value ELSE NULL END) AS lab_50885,
    MAX(CASE WHEN itemid = '50889' THEN value ELSE NULL END) AS lab_50889,
    MAX(CASE WHEN itemid = '50893' THEN value ELSE NULL END) AS lab_50893,
    MAX(CASE WHEN itemid = '50896' THEN value ELSE NULL END) AS lab_50896,
    MAX(CASE WHEN itemid = '50902' THEN value ELSE NULL END) AS lab_50902,
    MAX(CASE WHEN itemid = '50904' THEN value ELSE NULL END) AS lab_50904,
    MAX(CASE WHEN itemid = '50905' THEN value ELSE NULL END) AS lab_50905,
    MAX(CASE WHEN itemid = '50906' THEN value ELSE NULL END) AS lab_50906,
    MAX(CASE WHEN itemid = '50907' THEN value ELSE NULL END) AS lab_50907,
    MAX(CASE WHEN itemid = '50908' THEN value ELSE NULL END) AS lab_50908,
    MAX(CASE WHEN itemid = '50909' THEN value ELSE NULL END) AS lab_50909,
    MAX(CASE WHEN itemid = '50910' THEN value ELSE NULL END) AS lab_50910,
    MAX(CASE WHEN itemid = '50911' THEN value ELSE NULL END) AS lab_50911,
    MAX(CASE WHEN itemid = '50912' THEN value ELSE NULL END) AS lab_50912,
    MAX(CASE WHEN itemid = '50915' THEN value ELSE NULL END) AS lab_50915,
    MAX(CASE WHEN itemid = '50927' THEN value ELSE NULL END) AS lab_50927,
    MAX(CASE WHEN itemid = '50930' THEN value ELSE NULL END) AS lab_50930,
    MAX(CASE WHEN itemid = '50931' THEN value ELSE NULL END) AS lab_50931,
    MAX(CASE WHEN itemid = '50945' THEN value ELSE NULL END) AS lab_50945,
    MAX(CASE WHEN itemid = '50947' THEN value ELSE NULL END) AS lab_50947,
    MAX(CASE WHEN itemid = '50949' THEN value ELSE NULL END) AS lab_50949,
    MAX(CASE WHEN itemid = '50950' THEN value ELSE NULL END) AS lab_50950,
    MAX(CASE WHEN itemid = '50951' THEN value ELSE NULL END) AS lab_50951,
    MAX(CASE WHEN startdate IS NOT NULL THEN 1 ELSE 0 END) AS has_prescription
FROM 
    (
        SELECT 
            t1.subject_id,
            t1.hadm_id,
            t1.icd9_code,
            t2.admittime,
            t2.dischtime,
            t2.deathtime,
            t2.insurance,
            t2.marital_status,
            t3.gender,
            t3.dob,
            t3.dod,
            t3.expire_flag,
            t4.itemid,
            t4.value,
            t4.valuenum,
            t4.valueuom,
            t4.flag,
            t5.label,
            t5.category,
            t6.startdate,
            t6.enddate,
            t6.drug_type,
            t6.drug,
            t6.drug_name_poe,
            t6.drug_name_generic
        FROM 
            mimiciii.diagnoses_icd t1 
            LEFT JOIN mimiciii.admissions t2 ON t1.hadm_id=t2.hadm_id 
            LEFT JOIN mimiciii.patients t3 ON t1.subject_id=t3.subject_id 
            LEFT JOIN mimiciii.labevents t4 ON t1.hadm_id=t4.hadm_id 
            LEFT JOIN mimiciii.d_labitems t5 ON t4.itemid=t5.itemid 
            LEFT JOIN mimiciii.prescriptions t6 ON t1.hadm_id=t6.hadm_id 
        WHERE 
            t1.icd9_code IN ('41404','41030','41021','41042','41407') 
            AND t4.itemid IN ('50803','50804','50805','50806','50808','50809','50811','50813','50814','50815', '50816','50817','50818','50820','50821','50822','50824','50852','50853','50854', '50855','50861','50862','50863','50866','50867','50868','50869','50870','50878', '50882','50883','50884','50885','50889','50893','50896','50902','50904','50905', '50906','50907','50908','50909','50910','50911','50912','50915','50927','50930', '50931','50945','50947','50949','50950','50951')
    ) t
GROUP BY 
    subject_id,
    hadm_id,
    admittime,
    dischtime,
    deathtime,
    insurance,
    marital_status,
    gender,
    dob,
    dod,
    expire_flag

这个查询将每个病人的ICD-9诊断和实验室结果作为列返回，并将处方信息表示为二进制变量。如果病人有药物处方，则has_prescription列为1，否则为0。请注意，这只是一个示例查询，你需要根据你的数据进行修改。