SELECT
	R.[LN],
	MAX ( R.[HN] ) AS [HN],
	MAX ( R.[FULLNAME] ) AS [ชื่อ-สกุล],
	MAX ( R.[SEX] ) AS [เพศ],
	MAX ( R.[YEAR] ) AS [อายุ(ปี)],
	MAX ( R.[PATIENT TYPE NAME] ) AS [ประเภทผู้ป่วย],
	MAX ( R.[WARD NAME] ) AS [วอร์ด/แผนก],
	MAX ( R.[DOCTOR NAME] ) AS [แพทย์ผู้สั่ง],
	MAX ( R.[ORDER NO] ) AS [เลขที่ Order],
	MAX ( R.[REQ ITEM CHECKIN DATETIME] ) AS [วันที่ Checkin],
	MAX ( R.[PRIORITY NAME] ) AS [ความเร่งด่วน],
-- รวมรายการตรวจทั้งหมดที่สั่งใน LN นี้ (เฉพาะรายการที่ผ่านเงื่อนไข)
	STUFF(
		(
		SELECT
			', ' + R2.[REQ ITEM NAME] 
		FROM
			dbo.view_lab_statistic_Result_List R2 
		WHERE
			R2.[LN] = R.[LN] 
			AND R2.[ORDER INACTIVE] = 'N' 
			AND R2.[RES ITEM STATE] = 'A' 
			AND R2.[REQ STATE] <> 'R' 
			AND R2.[RES ITEM INACTIVE] = 'N' 
			AND R2.[RES ITEM REJECT] = 'N' 
		GROUP BY
			R2.[REQ ITEM NAME],
			R2.[REQ ITEM TEST SEQ] 
		ORDER BY
			R2.[REQ ITEM TEST SEQ] FOR XML PATH ( '' ),
			TYPE 
		).value ( '.', 'NVARCHAR(MAX)' ),
		1,
		2,
		'' 
	) AS [รายการตรวจทั้งหมด],
-- Pivot ผลตรวจตามรหัสที่สนใจ
	MAX ( CASE WHEN R.[RES ITEM CODE] = 'HM001' THEN R.[RES ITEM RESULT] END ) AS [Hb],
	MAX ( CASE WHEN R.[RES ITEM CODE] = 'HM003' THEN R.[RES ITEM RESULT] END ) AS [WBC],
	MAX ( CASE WHEN R.[RES ITEM CODE] = 'SP635' THEN R.[RES ITEM RESULT] END ) AS [Hct],
	MAX ( CASE WHEN R.[RES ITEM CODE] = 'HM014' THEN R.[RES ITEM RESULT] END ) AS [PLT],
	MAX ( CASE WHEN R.[RES ITEM CODE] = 'CH005' THEN R.[RES ITEM RESULT] END ) AS [Glucose],
	MAX ( CASE WHEN R.[RES ITEM CODE] = 'CH060' THEN R.[RES ITEM RESULT] END ) AS [BUN],
	MAX ( CASE WHEN R.[RES ITEM CODE] = 'CH059' THEN R.[RES ITEM RESULT] END ) AS [Cr],
MAX(CASE WHEN R.[RES ITEM CODE] = 'CH001' THEN R.[RES ITEM RESULT] END) AS [Blood Sugar],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH002' THEN R.[RES ITEM RESULT] END) AS [BUN],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH003' THEN R.[RES ITEM RESULT] END) AS [Creatinine],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH086' THEN R.[RES ITEM RESULT] END) AS [eGFR],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH004' THEN R.[RES ITEM RESULT] END) AS [Uric_Acid],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH005' THEN R.[RES ITEM RESULT] END) AS [Cholesterol],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH006' THEN R.[RES ITEM RESULT] END) AS [Triglyceride],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH007' THEN R.[RES ITEM RESULT] END) AS [HDL_C],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH008' THEN R.[RES ITEM RESULT] END) AS [LDL_C],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH009' THEN R.[RES ITEM RESULT] END) AS [Total_Protein],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH010' THEN R.[RES ITEM RESULT] END) AS [Albumin],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH012' THEN R.[RES ITEM RESULT] END) AS [T_Bilirubin],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH013' THEN R.[RES ITEM RESULT] END) AS [D_Bilirubin],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH015' THEN R.[RES ITEM RESULT] END) AS [SGOT],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH016' THEN R.[RES ITEM RESULT] END) AS [SGPT],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH017' THEN R.[RES ITEM RESULT] END) AS [ALP],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH020' THEN R.[RES ITEM RESULT] END) AS [Sodium],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH021' THEN R.[RES ITEM RESULT] END) AS [Potassium],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH022' THEN R.[RES ITEM RESULT] END) AS [Chloride],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH025' THEN R.[RES ITEM RESULT] END) AS [Calcium],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH033' THEN R.[RES ITEM RESULT] END) AS [HbA1C],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH034' THEN R.[RES ITEM RESULT] END) AS [Blood_Ketone],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH040' THEN R.[RES ITEM RESULT] END) AS [Sugar],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH045' THEN R.[RES ITEM RESULT] END) AS [Glucose],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH070' THEN R.[RES ITEM RESULT] END) AS [Hct],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH081' THEN R.[RES ITEM RESULT] END) AS [Hb],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH106' THEN R.[RES ITEM RESULT] END) AS [Anion_Gap],
    MAX(CASE WHEN R.[RES ITEM CODE] = 'CH151' THEN R.[RES ITEM RESULT] END) AS [CRP]
 

FROM
	dbo.view_lab_statistic_Result_List R 
WHERE
	R.[REQ ITEM CHECKIN DATETIME]  BETWEEN '@d1' AND '@d2'
	AND R.[ORDER INACTIVE] = 'N' 
	AND R.[RES ITEM STATE] = 'A' 
	AND R.[REQ STATE] <> 'R' 
	AND R.[RES ITEM INACTIVE] = 'N' 
	AND R.[RES ITEM REJECT] = 'N' 
GROUP BY
	R.[LN] 
ORDER BY
	R.[LN] DESC;