cancel
Showing results for 
Search instead for 
Did you mean: 

DYNAMIC PIVOT - Cannot Execute in Query

Former Member
0 Kudos
151

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


Accepted Solutions (1)

Accepted Solutions (1)

edy_simon
Active Contributor

Hi,

Remove the

PRINT @COLS

Before the Set @A=

Regards

Edy


Former Member
0 Kudos

Dear Edy,

Thanks for your help.

Answers (1)

Answers (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos