SELECT * FROM 
 (
SELECT
        LEFT(CONVERT(varchar, [ORDER DATETIME], 120), 16)            as [Order D  /T] ,
        LEFT(CONVERT(varchar, [REQ ITEM CHECKIN DATETIME], 120), 16) as [Receive D/T] ,
        LEFT(CONVERT(varchar, [RES ITEM APPROVE DATETIME], 120), 16) as [Approve D/T] ,
        LN                                                           as 'LAB NO.'     ,
        [HN]                                                         as 'PID'         ,
        [REF ID]                                                     as'CID'          ,
        [FULLNAME]                                                   as'FULL NAME'    ,
        [BIRTHDATE]                                                  as'BIRTHDATE'    ,
        [ORDER NO]                                                   as'ORDER NO.'    ,
        [YEAR]                                                       as 'AGE'         ,
        [SEX]                                                        as 'SEX'         ,
      [SOURCE NAME]                                                as SOURCE        ,
        [WARD NAME]                                                  as WARD          ,
        [DOCTOR NAME]                                                as CLINICIAN     ,
        [RES ITEM RESULT                                             ]                ,
        [RES ITEM NAME                                               ]
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] in ('MB105',
                            'MB053',
                            'SP189')
and     [RES ITEM STATE] = 'A'
AND     [ORDER INACTIVE] = 'N'
AND     [REQ STATE]      <> 'R' And ([REQ ITEM CHECKIN DATETIME] between '2025-01-04 00:00' and '2025-01-04 23:59')
 ) t PIVOT(
MAX  ([RES ITEM RESULT])
FOR [RES ITEM NAME]   in ([Hemoculture I],[Hemoculture II],[Lactate])
 ) AS pivot_table;




----บางที่
/*
SELECT
    LEFT(CONVERT(varchar, o.[ORDER DATETIME], 120), 16) AS [Order D/T],
    LEFT(CONVERT(varchar, o.[REQ ITEM CHECKIN DATETIME], 120), 16) AS [Receive D/T],
    LEFT(CONVERT(varchar, o.[RES ITEM APPROVE DATETIME], 120), 16) AS [Approve D/T],
    o.LN AS 'LAB NO.',
    o.HN AS 'PID',
    o.[REF ID] AS 'CID',
    p.[FULLNAME] AS 'NAME',
    o.[RES ITEM NAME],
    o.[RES ITEM RESULT],
    p.[YEAR] AS AGE,
    p.SEX,
    o.[SOURCE NAME] AS [SOURCE],
    o.[WARD NAME] AS WARD,
    o.[DOCTOR NAME] AS CLINICIAN
FROM 
    view_lab_statistic_Result_List o
JOIN 
    tbl_lab_information_Patient p ON o.HN = p.IPT_PID
WHERE  
    o.[RES ITEM CODE] IN ('HM003', 'HM048', 'HM002', 'HM054', 'HM004', 'HM078', 'HM006', 'HM001', 'HM035', 'HM036', 'HM014', 'HM009', 'HM012', 'HM013', 'HM010', 'HM011', 'HM097', 'HM005', 'HM016', 'HM118', 'HM007', 'HM015')
    AND o.[RES ITEM STATE] = 'A'
    AND o.[ORDER INACTIVE] = 'N'
    AND o.[REQ STATE] <> 'R'
*/