DYNAMIC PIVOT - Cannot Execute in Query

96 Views

Hi,

I'm working on a Query that makes use of Dynamic Pivot.

This is my query:

------------------------------------------------------------------------------------------------------------------------------

DECLARE @cols NVARCHAR(MAX)= N''

DECLARE @A NVARCHAR(MAX)

SELECT @cols = @cols + CASE WHEN @cols = N'' THEN QUOTENAME(OWHS.WhsCode) ELSE N',' + QUOTENAME(OWHS.WhsCode) END FROM OWHS

PRINT @COLS

SET @A = 'SELECT * FROM (SELECT OIBT.ItemCode, OITM.ItemName,OITM.Spec, OIBT.BatchNum, OIBT.ExpDate, OIBT.WhsCode, ISNULL(OIBT.Quantity/OITM.NumInBuy,0) AS Quantity ,ISNULL(OITW.OnHand/OITM.NumInBuy,0) AS OnHand, ISNULL(OITM.OnHand/OITM.NumInBuy,0) AS STOCK

FROM OIBT INNER JOIN OITM ON OIBT.ItemCode = OITM.ItemCode INNER JOIN OITW ON OIBT.ItemCode = OITW.ItemCode AND OIBT.WhsCode = OITW.WhsCode

WHERE OIBT.Quantity>0

GROUP BY OIBT.ItemCode, OITM.ItemName,OITM.Spec, OIBT.BatchNum, OIBT.ExpDate, OIBT.WhsCode, OIBT.Quantity, OITM.NumInBuy, OITW.OnHand, OITM.OnHand ) AS GROUPTABLE

PIVOT

(SUM(GROUPTABLE.Quantity)

FOR GROUPTABLE.WhsCode IN('

+ @cols

+ ')

)AS pvt'

EXEC (@A)

------------------------------------------------------------------------------------------------------------------------------

When I execute this SQL in Query Preview, I will got an error message:

" 1). [Microsoft][SQL Server Native Client 10.0]Invalid cursor state ".

Even though I add this SQL in PROCEDURE, it still cannot execute and get the same message.

Hopefully expert can help me.

Best Regards,

Sharon