Query รายงานและแสดงผล lab ในแนวนอนมาดูกัน

สวัสดีค่ะ วันก่อนมี it โรงพยาบาลสอบถามมาเรื่องจะทำรายงานยังไงให้นำผล Lab มาแสดงในแนวนอนได้ จะบอกว่าดีใจมากเลยค่ะที่น้องตั้งใจและคอยศึกษาเพิ่มเติมอยู่เสมอ เมื่อติดปัญหาก็นึกถึง (ดีใจจริงๆนะเนี๊ยะ) และก็คิดว่าสิ่งนี้น่าจะเป็นประโยชน์สำหรับอีกหลายๆคนที่กำลังทำรายงานด้วยตนเองอยู่ อย่างไรก็ตามขอให้มี case มาถามบ่อยๆนะคะ จะได้นำมาเผยแพร่ให้กับทุกคนได้ร่วมเรียนรู้และเดินไปด้วยกันค่ะ

สำหรับคำถามที่ถามมาก็ตอบไปค่ะ แต่ก็ไม่ได้เขียนให้นะ ก็แนะนำไปค่ะ ตอนนี้ก็ไม่รู้ว่าได้ผลยังไงบ้างแต่ก็คงเพราะโอ๋เองไม่ค่อยได้ online (บ้านนอก..on กับมือถือ อิอิ) 

คำถามหากวิเคราะห์ออกมาเป็น Output ที่ต้องการก็น่าจะเป็น

namepat	hn	pid	vn	CHOLESTEROL	TRIGRYCERIDE	HDL-CHOLESTEROL	LDL-CHOLESTERAL[CALCULATE]
ทดสอบ1	490004971	3461200371659	530014001	191	105	32	138
ทดสอบ2	470002778		530014001	275	201	56	179

จาก Output ที่ต้องการนั้น โดยความสามารถของ PostgreSQL8.0 ยังไม่มีในส่วนของการทำ Crosstab Table ค่ะ (version ใหม่ๆ เหมือนจะมีแล้วนะคะ...มั้ง) 
ที่จริงหากไม่ต้องการทำอะไรให้ยุ่งยาก หรือไม่ต้องการเขียน Query ที่ยุ่งยาก ก็นำผลที่เราเขียน Query นั้น Save เป็น Excel หลังจากนั้นก็ใช้ตวามสามารถของ Excel ต่อได้เลยค่ะ ง่ายๆ สบายมาก หรืออีกวิธี คือ เราก็ทำ Query ใน Asscess ไปเลยมี crosstab อยู่แล้วค่ะ เพียงเรา Link ข้อมูลเข้าไปที่ Access ก็สามารถทำ Query ได้ค่ะ

แต่ถ้าต้องการให้ได้ข้อมูลในผลที่ได้จาก QUery ของเราก็พอมีทางอยู่ค่ะ แต่มีข้อจำกัดพอสมควร คือ ต้องทำการ Fixed เฉพาะข้อมูล Lab ที่ต้องการเท่านั้น ในที่นี้เราต้องการทราบ LIPID PROFILE ซึ่งมี Lab test อยู่ 4 ตัว คือ CHOLESTEROL,TRIGRYCERIDE,HDL-CHOLESTEROL,LDL-CHOLESTERAL[CALCULATE] เรารู้แน่ว่าเราต้องการผลนี้ และในฐานข้อมูลเราก็ Set item สำหรับใช้งานเรียบร้อยแล้ว ก็มาเริ่มต้นทำงานกันค่ะ  ก่อนทำงานสำรวจข้อมูล ดังนี้
รหัสของ LIPID PROFILE ในตาราง b_item
174000009974453508    C481    LIPID PROFILE

ชื่อของ LAB Test ที่ใช้
CHOLESTEROL
TRIGRYCERIDE
HDL-CHOLESTEROL
LDL-CHOLESTERAL[CALCULATE]

ขั้นที่ 1 เราจะต้อง Query ข้อมูลให้ได้ตามที่เราต้องการขึ้นมาก่อน
select
(t_patient.patient_firstname || '  ' || t_patient.patient_lastname) as namepat
,t_patient.patient_hn as hn
, t_patient.patient_pid as pid
, t_visit.visit_vn as vn
, t_order.order_common_name as labname
, t_result_lab.result_lab_name as labtest
, t_result_lab.result_lab_value as labval
, t_result_lab.result_lab_unit as labunit
from t_patient inner join t_visit on t_patient.t_patient_id = t_visit.t_patient_id
inner join t_order on t_order.t_visit_id = t_visit.t_visit_id
inner join t_result_lab on t_order.t_order_id = t_result_lab.t_order_id
where  t_order.b_item_id = '174000009974453508' --รหัสของ LIPID PROFILE
and t_order.f_order_status_id = '4' --สถานะที่รายงานผลเรียบร้อยแล้ว
and t_visit.f_visit_status_id <> '4' --ต้องไม่ยกเลิกการเข้ารับบริการ
and t_result_lab.result_lab_active = '1' --ไม่ถูกยกเลิกผล
and substring(t_visit.visit_financial_discharge_time,1,10) Between '2553-05-31' and '2553-05-31' --วันที่ที่เราสนใจจากวันที่จำหน่ายออก หรือเราจะเลือกใช้วันอื่นก็ได้เช่น วันที่สั่ง lab วันที่เข้ารับบริการ

ผลลัพธ์ที่จะได้ คือ Record ที่เท่ากับจำนวน LABTEST รวมกัน คือ 1 visit จะมี 4 LABTEST นั่นคือเราจะได้ visit ละ 4 record

namepat	hn	pid	vn	labname	labtest	labval	labunit
ทดสอบ1  ผลแลป1	510006338		053017052	LIPID PROFILE	CHOLESTEROL	215	mg/dl [125-200]
ทดสอบ1  ผลแลป1	510006338		053017052	LIPID PROFILE	TRIGRYCERIDE	113	mg/dl [45 - 150 ]
ทดสอบ1  ผลแลป1	510006338		053017052	LIPID PROFILE	HDL-CHOLESTEROL	71	mg/dl [40 - 60 ]
ทดสอบ1  ผลแลป1	510006338		053017052	LIPID PROFILE	LDL-CHOLESTERAL[CALCULATE]	121	mg/dl [0-130]
ทดสอบ2  ผลแลป2	350022285		053017048	LIPID PROFILE	CHOLESTEROL	139	mg/dl [125-200]
ทดสอบ2  ผลแลป2	350022285		053017048	LIPID PROFILE	TRIGRYCERIDE	206	mg/dl [45 - 150 ]
ทดสอบ2  ผลแลป2	350022285		053017048	LIPID PROFILE	HDL-CHOLESTEROL	39	mg/dl [40 - 60 ]
ทดสอบ2  ผลแลป2	350022285		053017048	LIPID PROFILE	LDL-CHOLESTERAL[CALCULATE]	59	mg/dl [0-130]
ทดสอบ 3  ผลแลป 3	500000960		053017086	LIPID PROFILE	CHOLESTEROL	188	mg/dl [125-200]
ทดสอบ 3  ผลแลป 3	500000960		053017086	LIPID PROFILE	TRIGRYCERIDE	125	mg/dl [45 - 150 ]
ทดสอบ 3  ผลแลป 3	500000960		053017086	LIPID PROFILE	HDL-CHOLESTEROL	39	mg/dl [40 - 60 ]
ทดสอบ 3  ผลแลป 3	500000960		053017086	LIPID PROFILE	LDL-CHOLESTERAL[CALCULATE]	124	mg/dl [0-130]

ขั้นที่ 2 นำเอา Output จากข้อแรกมา ทำการจัดรูปแบบแสดงผลใหม่ โดยใช้ความสามารถของ Case....when....then....else นะคะ
select
(t_patient.patient_firstname || '  ' || t_patient.patient_lastname) as namepat
,t_patient.patient_hn as hn
, t_patient.patient_pid as pid
, t_visit.visit_vn as vn
, case when t_result_lab.result_lab_name ilike 'CHOLESTEROL'
            then t_result_lab.result_lab_value
            else ''
  end as "CHOLESTEROL"
, case when t_result_lab.result_lab_name ilike 'TRIGRYCERIDE'
            then t_result_lab.result_lab_value
            else ''
  end as "TRIGRYCERIDE"
, case when t_result_lab.result_lab_name ilike 'HDL-CHOLESTEROL'
            then t_result_lab.result_lab_value
            else ''
  end as "HDL-CHOLESTEROL"
, case when t_result_lab.result_lab_name ilike 'LDL-CHOLESTERAL[CALCULATE]'
            then t_result_lab.result_lab_value
            else ''
  end as "LDL-CHOLESTERAL[CALCULATE]"
from t_patient inner join t_visit on t_patient.t_patient_id = t_visit.t_patient_id
inner join t_order on t_order.t_visit_id = t_visit.t_visit_id
inner join t_result_lab on t_order.t_order_id = t_result_lab.t_order_id
where  t_order.b_item_id = '174000009974453508' --รหัสของ LIPID PROFILE
and t_order.f_order_status_id = '4' --สถานะที่รายงานผลเรียบร้อยแล้ว
and t_visit.f_visit_status_id <> '4' --ต้องไม่ยกเลิกการเข้ารับบริการ
and t_result_lab.result_lab_active = '1' --ไม่ถูกยกเลิกผล
and substring(t_visit.visit_financial_discharge_time,1,10) Between '2553-05-31' and '2553-05-31' --วันที่ที่เราสนใจจากวันที่จำหน่ายออก หรือเราจะเลือกใช้วันอื่นก็ได้เช่น วันที่สั่ง lab วันที่เข้ารับบริการ

ผลลัพธ์ที่จะได้ คือ Record ยังคงเป็น 4 record แต่เราจะได้ Column ของผล LAB แยกเป็น 4 ตัว

namepat	hn	pid	vn	CHOLESTEROL	TRIGRYCERIDE	HDL-CHOLESTEROL	LDL-CHOLESTERAL[CALCULATE]
ทดสอบ1  ผลแลป1	510006338		053017052	215
ทดสอบ1  ผลแลป1	510006338		053017052		113
ทดสอบ1  ผลแลป1	510006338		053017052			71
ทดสอบ1  ผลแลป1	510006338		053017052				121
ทดสอบ2  ผลแลป2	350022285		053017048	139
ทดสอบ2  ผลแลป2	350022285		053017048		206
ทดสอบ2  ผลแลป2	350022285		053017048			39
ทดสอบ2  ผลแลป2	350022285		053017048				59
ทดสอบ 3  ผลแลป 3	500000960		053017086	188
ทดสอบ 3  ผลแลป 3	500000960		053017086		125
ทดสอบ 3  ผลแลป 3	500000960		053017086			39
ทดสอบ 3  ผลแลป 3	500000960		053017086				124

ขั้นที่ 3 จากขั้นที่สองไม่ได้ตามที่ต้องการเพราะ Record เบิ้ลอยู่ค่ะ ก็ทำให้มันสวยซะ ในขั้นนี้ใช้การลักไก่นิดหน่อยค่ะ อิอิ ....เราจะสามารถลักไก่ในขั้นที่สามได้ เราจะต้องตั้งใจทำให้เกิดการลักไก่

จากขั้นที่สองค่ะ ลองสังเกตดูนะว่าใน case ของเราทำอะไรไว้บ้าง

, case when t_result_lab.result_lab_name ilike 'CHOLESTEROL'
    then t_result_lab.result_lab_value --ค่านี้เป็นค่าของ Lab ค่ะ จะเป็นอะไรก็แล้วแต่ขึ้นอยู่กับผลที่ได้
    else '' --อันนี้แหละเป็นค่า '' ซึ่งนำมาลักไก่ได้ค่ะ
end as "CHOLESTEROL"

เราจะใช้ความสามารถของ function max(...) เข้ามาช่วยค่ะ max ก็คือ ค่าที่มากที่สุดเพียงค่าเดียวค่ะ ดูจาก case ที่เราสร้างไว้หลัง else ถ้าเข้าเงื่อนไขนี้จะเป็นค่าว่างเสมอนะคะ ส่วนหลัง then นั้นจะเป็นค่าอะไรก็แล้วแต่ ในความหมายของมันคือ ต้องมากกว่า '' แน่นอนค่ะ  ที่สำคัญคือ การที่เราจะใช้ function max ได้เราจะต้องบอกว่าเรา max ตามค่าอะไรบ้าง นั่นคือต้อง group by ให้มันทราบค่ะ จะได้ทำงานถูกต้อง ทั้นี้เราต้องการข้อมูลของคนๆนึง ดังนั้น Group ที่จะจับคือ คนนั้น นั่นเอง

select
(t_patient.patient_firstname || '  ' || t_patient.patient_lastname) as namepat
,t_patient.patient_hn as hn
, t_patient.patient_pid as pid
, t_visit.visit_vn as vn
, max(case when t_result_lab.result_lab_name ilike 'CHOLESTEROL'
            then t_result_lab.result_lab_value
            else ''
  end) as "CHOLESTEROL"
, max(case when t_result_lab.result_lab_name ilike 'TRIGRYCERIDE'
            then t_result_lab.result_lab_value
            else ''
  end) as "TRIGRYCERIDE"
, max(case when t_result_lab.result_lab_name ilike 'HDL-CHOLESTEROL'
            then t_result_lab.result_lab_value
            else ''
  end) as "HDL-CHOLESTEROL"
, max(case when t_result_lab.result_lab_name ilike 'LDL-CHOLESTERAL[CALCULATE]'
            then t_result_lab.result_lab_value
            else ''
  end) as "LDL-CHOLESTERAL[CALCULATE]"
from t_patient inner join t_visit on t_patient.t_patient_id = t_visit.t_patient_id
inner join t_order on t_order.t_visit_id = t_visit.t_visit_id
inner join t_result_lab on t_order.t_order_id = t_result_lab.t_order_id
where  t_order.b_item_id = '174000009974453508' --รหัสของ LIPID PROFILE
and t_order.f_order_status_id = '4' --สถานะที่รายงานผลเรียบร้อยแล้ว
and t_visit.f_visit_status_id <> '4' --ต้องไม่ยกเลิกการ เข้ารับบริการ
and t_result_lab.result_lab_active = '1' --ไม่ถูกยกเลิกผล
and substring(t_visit.visit_financial_discharge_time,1,10) Between '2553-05-31' and '2553-05-31' --วันที่ที่เราสนใจจากวันที่ จำหน่ายออก หรือเราจะเลือกใช้วันอื่นก็ได้เช่น วันที่สั่ง lab วันที่เข้ารับบริการgroup by namepat,hn,pid,vn --เราจะต้องนำ column ที่เราไม่ได้ใช้กับ function มา group by ทั้งหมดนะคะ ในที่นี้ใช้ชื่อที่ as ไว้แล้วได้เลยค่ะ

ผลลัพธ์ที่จะได้ คือ ได้ข้อมูลผล LAB ตามแนวนอนแล้วค่ะ และไม่เบิ้ลด้วย

namepat	hn	pid	vn	CHOLESTEROL	TRIGRYCERIDE	HDL-CHOLESTEROL	LDL-CHOLESTERAL[CALCULATE]
ทดสอบ 3  ผลแลป 3	500000960		053017086	188	125	39	124
ทดสอบ2  ผลแลป2	350022285		053017048	139	206	39	59
ทดสอบ1  ผลแลป1	510006338		053017052	215	113	71	121

ทิ้งท้ายเอาไว้ แล้วถ้าเราต้องการดู Lab 2 ตัวล่ะ จะเพิ่มเข้าไปยังไงดี ลองทำขั้นตอนที่ 4 ต่อนะคะ  หรือเราต้องการค่าของวันที่ตรวจเพิ่มเติม ทำยังไงดี .... ทุกอย่างมีคำตอบ หากลงมือทำ ....
ดังนั้น เราก็ได้สิ่งที่เราต้องการแล้วค่ะ จริงๆเราก็ต้องดูเป็นขั้นตอนว่าข้อมูลมายังไง เราจึงจะทำตรงนี้ได้ค่ะ ค่อยๆทำทีละขั้น และทำความเข้าใจ และหาทางแก้มันซะ มันไม่ยากหากเราตั้งใจค่ะ สู้ๆ สู้ๆ สู้ตาย ......

และขอบคุณ อิง โรงพยาบาลเทพา ที่ถามคำถามชวนตอบค่ะ