SELECT 
tbl_lab_information_Patient.IPT_TITLE + '  ' + tbl_lab_information_Patient.IPT_FNAME + ' ' + tbl_lab_information_Patient.IPT_LNAME AS 'ชื่อ'
,[YEAR] as 'อายุ'
,[HN] 	as 'HN'
,[PATIENT TYPE NAME] as 'สิทธิการรักษา'
,(SELECT CAST(DATEPART(YYYY,[IREQ_LAST_CHK_DT]) AS VARCHAR) + '/' + CAST(DATEPART(MM,[IREQ_LAST_CHK_DT]) AS VARCHAR) + '/' + CAST(DATEPART(DD,[IREQ_LAST_CHK_DT]) AS VARCHAR) + ' ' + CONVERT(char(5), [IREQ_LAST_CHK_DT], 108)) as 'เวลาเจาะเลือด'
,'' as 'เวลาย้อม CD4'
,'' as '% CD4'
,'' as 'Abs.CD4'
,[RES ITEM NAME]
,[RES ITEM RESULT] as 'results_pvt'
,'' 	as 'หมายเหตุ'

FROM view_lab_statistic_Result_List
join tbl_lab_information_Patient on view_lab_statistic_Result_List.HN = tbl_lab_information_Patient.IPT_PID

where  
([RES ITEM CODE] = 'HM003' OR [RES ITEM CODE] = 'HM007'  OR [RES ITEM CODE] = 'SP078' OR [RES ITEM CODE] = 'SP079' OR [RES ITEM CODE] = 'SP0781')
--[RES ITEM CODE]  IN ('HM003','HM007','SP078','SP079','SP0781')

AND [RES ITEM STATE] = 'A'  AND [ORDER INACTIVE]  = 'N' AND ([RES ITEM TYPE] = 'P'  or [RES ITEM RESULT] <> '')
and (
select count(*)
from view_lab_statistic_Result_List as i
where i.HN = view_lab_statistic_Result_List.HN and i.[RES ITEM CODE] = 'SP078' and CONVERT(date, i.IREQ_LAST_CHK_DT) = CONVERT(date, view_lab_statistic_Result_List.IREQ_LAST_CHK_DT)
) > 0

/*) t
PIVOT(
*/

MAX  ([results_pvt])
FOR [RES ITEM NAME]   in ([WBC Count],[Lymphocyte],[Nab No])


/*) AS pivot_table;
*/