Pivot Query Problem
Posted on 2016-10-10
The source table and Pivot query code are below. The query executes perfectly with 28 WrkCtr columns. However when I add data to the source table that requires the 29th I receive the following error. What would cause this error ? Is there a limit to the number of columns ?
Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string '54 ---#54-Gardner Db'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '54 ---#54-Gardner Db'.
The source for the pivot query is a table formatted as follows:
Item Qty Value WrkCtr Cycle
A_1000 7000 23000 1----100T Press .0030
A_1000 7000 23000 33---Large Grinder .0005
A_2000 10000 50000 1---100T Press .0020
A_2000 10000 50000 99--500T press .0009
The Pivot Query code is as follows:
Declare @sql As NVARCHAR(2000); Declare @col As NVARCHAR(2000);
Select @col = ISNULL(@col + ', ','') + QUOTENAME(WrkCtr)
From (Select Distinct WrkCtr From dbo.Item_WC) As Items;
Set @sql =
N'Select ITEM,Qty,Vaue, ' + @col +
For WrkCtr IN (' + @col + ')) As PivotTable';
Exec sp_executesql @sql;