Hi
In below code i am getting error "Incorrect Syntax near 'NA' in set @query = syntax
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)
Thanks