How to use Pivot with Join Table to convert row to column in SQL

Dear All,

Based on below table show the original database,  i want convert the row data to column.

i use the pivot function in the script, it show an error. Kindly advise. thank you

SELECT 
(CASE WHEN (CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')) <> '' THEN (CASE WHEN (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0401' OR RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0601' OR RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0801' OR RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0901' OR RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '1101') AND (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),15),6) <= '070000') THEN  CAST(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8) AS INT) + 30 - 100 ELSE(CASE WHEN (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0501' OR RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0701' OR RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '1001' OR RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '1201') AND (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),15),6) <= '070000')THEN  CAST(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8) AS INT) + 30 - 101 ELSE(CASE WHEN (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0301') AND (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),15),6) <= '070000') THEN  CAST(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8) AS INT) + 27 - 100 ELSE (CASE WHEN (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8),4) = '0101') AND (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),15),6) <= '070000') 
THEN  CAST(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8) AS INT) + 1130 - 10000 ELSE (CASE WHEN (RIGHT(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),15),6) <= '070000') THEN CAST(LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8) AS INT) - 1 ELSE LEFT((CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,112) +  ' ' + REPLACE(CONVERT(VARCHAR(10),INVTRANS.INV_CREATE_DATE,108),':','')),8) END)END)END)END)END)END) AS TRANS_DATE
,INVDET.INV_TYPE
,INVTRANS.INV_ID
,INVDET.INV_SHORTDESC
,INVTRANS.INV_TRANS_QTY AS MI_QTY
FROM CIMProRPT01.dbo.OTH_INV_TRANSACTION INVTRANS


JOIN 

( SELECT

  INVDET.INV_TYPE
   FROM 

( SELECT * FROM CIMProRPT01.dbo.OTH_INV_DETAILS) INVDET

PIVOT (SUM(MI_QTY) FOR INV_TYPE IN (CS-5S,CS-Others))P


) INVDET

ON INVTRANS.INV_ID = INVDET.INV_ID;