DYNAMIC PIVOT - Cannot Execute in Query

[Former Member]

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

[edy_simon · Accepted solution]

Hi,

Remove the

PRINT @COLS

Before the Set @A=

Regards

Edy

[Former Member · Accepted solution]

Dear Edy,

Thanks for your help.

[kothandaraman_nagarajan]

Hi,

You may refer below threads:

http://scn.sap.com/thread/1293128

http://scn.sap.com/thread/1540127

http://scn.sap.com/message/7572283#7572283

Thanks & Regards,

Nagarajan