"Incorrect Syntax near 'NA'

The error "Incorrect Syntax near 'NA'" in your SQL code is likely due to a syntax issue in setting the @query variable. The problem arises from using single quotes inside the dynamic SQL string where you are comparing T2.U_A_M to the string 'NA'.

To resolve this error, you need to escape the single quotes around 'NA' within the dynamic SQL string. You can achieve this by doubling the single quotes to escape them properly. Here's how you can modify your code snippet to address this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(T2."U_A_M") 
            FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> 'NA'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
    
set @query = 'SELECT itemcode, ' + @cols + ' from 
            (
                SELECT T1."ItemCode",T1."LineTotal", T2."U_A_M"
    FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> ''NA''
           ) x
            pivot 
            (
                 sum(linetotal)
                for category in (' + @cols + ')
            ) p '


execute(@query)

By doubling the single quotes around 'NA' to become ''NA'', you ensure that the SQL query is correctly formed and addresses the syntax error related to 'NA'. This modification should help in executing the SQL query successfully without encountering the incorrect syntax issue near 'NA'.

If you have any further questions or encounter any other challenges, feel free to ask!