id | Name | Test Name | Test Performed | Test Value
1    John     HIV         EIA            Reactive
1    John     HIV         EIA2           Reactive
1    John     HIV         WB             Positive
1    John     HIV         Final Kit      Positive
2    Mark     HIV         Rapid          Reactive
2    Mark     HIV         EIA            Reactive
2    Mark     HIV         EIA2           Reactive
2    Mark     HIV         Final Result   Positive
2    Mark     Immuno      CD4%           12.0%
2    Mark     Immuno      CD4 Lympo      1299
2    Mark     Immuni      CD4 Absolute   838
3    Carl     SY          RPR            Reactive
3.   Carl     SY          TPHA           Reactive

id | Name | Test Name | EIA      | EIA2   | Rapid |  WB         |Final Kit
1    John    HIV        Reactive  Reactive           Positive
2    Mark    HIV        Reactive  Reactive           Reactive    Reactive

id | Name | Test Name | CD4% | CD4 Lympo | CD4 Absolute
1    Mark   Immuno      12.0%  1299         838


id | Name | Test Name | RPR    | TPHA
3    Carl    SY        Reactive   Reactive

select (p.id) as Patient_ID, p.entry_datetime, po.specimen_id, CONCAT(p.last_name, ', ' ,p.first_name, ' ' ,p.middle_name) as Patient_Name,
p.alias, (poef.value) as Source, (sl.region) as Region, (sl.type) as Lab_Type, (tg.name) as TestName, (tc.name) as Test_Performed, (tor.value) as Test_Value , tr.release_date from patients p
right join patient_orders po on po.patient_id = p.id
left join saccl_referrals sr on sr.specimen_id = po.specimen_id
left join saccl_laboratories sl on sl.id = sr.laboratory_id
left join test_order_details tod on tod.specimen_id = po.specimen_id
left join test_orders ts on po.specimen_id = ts.specimen_id
left join test_results tr on ts.id = tr.test_order_id
left join test_groups tg on tg.id = tr.test_group_id
left join test_order_results tor on tor.test_order_detail_id = tod.id
left join test_codes tc on tod.test_id = tc.id
left join patient_order_extra_fields poef on po.specimen_id = poef.specimen_id where poef.extra_field_id = 'source'
order by entry_datetime desc