StampIT
asked on
Pivot Query Problem
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;
Print @col
Set @sql =
N'Select ITEM,Qty,Vaue, ' + @col +
'From dbo.Item_WC
PIVOT (Min(Cycle)
For WrkCtr IN (' + @col + ')) As PivotTable';
Exec sp_executesql @sql;
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;
Print @col
Set @sql =
N'Select ITEM,Qty,Vaue, ' + @col +
'From dbo.Item_WC
PIVOT (Min(Cycle)
For WrkCtr IN (' + @col + ')) As PivotTable';
Exec sp_executesql @sql;
Please find the fix below-
Output
ITEM Qty Value 1 33 99
A_1000 7000 23000 0.003 0.0005 NULL
A_2000 10000 50000 0.002 NULL 0.0009
I hope it helps.
CREATE TABLE Item_WC
(
Item VARCHAR(100)
,Qty INT
,Value INT
,WrkCtr INT
,Cycle FLOAT
)
GO
INSERT INTO Item_WC VALUES
('A_1000', 7000 , 23000 , 1 , .0030),
('A_1000', 7000 , 23000 , 33, .0005),
('A_2000', 10000 , 50000 , 1, .0020),
('A_2000', 10000 , 50000 , 1, .0020),
('A_2000', 10000 , 50000 , 99, .0009)
GO
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,Value, ' + @col +
'From dbo.Item_WC
PIVOT (Min(Cycle)
For WrkCtr IN (' + @col + ')) As PivotTable';
PRINT @sql
EXEC SP_EXECUTESQL @sql;
Output
ITEM Qty Value 1 33 99
A_1000 7000 23000 0.003 0.0005 NULL
A_2000 10000 50000 0.002 NULL 0.0009
I hope it helps.
ASKER
Pawan Kumar Khowal,
I do not see a difference between the code I posted and your fix. Am I missing something ?
Sharath,
Not sure what you are asking. Below the value of @sql from my code:
Select ID_ITEM,Qty,Value, [1 ---#1 - 21 1/2 AC 60T Press], [101 ---#101 -Vibratory Bowl (20 c.f.)], [12 ---#12 - 60T Automatic], [139 ---#139 - Shear LG], [156 ---#156 - Parts Washer(28'' Wide)], [157 ---#157 - Parts Washer(22''Wide)], [17 ---#17 - 21 1/2 AC 60T Press], [195 ---#195-Bruderer Roll Lvler], [2 ---#2 - 110T Clearing], [21 ---#21 - 150T Minister Press], [22 ---#22 - Keyence Ink Jet Printer], [237 ---#237 Vibratory Bowl (15 c.f.)], [26 ---#26 - 20B 32T Press], [28 ---#28 - 21 1/2 60T Press], [290 ---#290 - Horz 8'' Quench], [291 ---#291 - Vent 12'' Quench], [292 ---#292 - Horz 12'' Quench], [294 ---#294 - Horz 12'' Die Quench], [304 ---#304 - Horz 10'' Quench], [318 ---#318 - 250T Automatic], [319 ---#319 - 200T Aida], [32 ---#32 - 100T M-8 Press], [33 ---#33-Gardner Dbl Disc26''], [34 ---#34 Blanchard 42''], [343 ---#343 - 600T Verson], [346 ---#346 - 800T Verson], [446 ---#446 -VF3SS Vrt Mach Ctr], [54 ---#54-Gardner Dbl Disc30''], [96 ---#96 - 600T Bliss]From dbo.Item_WC
PIVOT (Min(Cycle)
For WrkCtr IN ([1 ---#1 - 21 1/2 AC 60T Press], [101 ---#101 -Vibratory Bowl (20 c.f.)], [12 ---#12 - 60T Automatic], [139 ---#139 - Shear LG], [156 ---#156 - Parts Washer(28'' Wide)], [157 ---#157 - Parts Washer(22''Wide)], [17 ---#17 - 21 1/2 AC 60T Press], [195 ---#195-Bruderer Roll Lvler], [2 ---#2 - 110T Clearing], [21 ---#21 - 150T Minister Press], [22 ---#22 - Keyence Ink Jet Printer], [237 ---#237 Vibratory Bowl (15 c.f.)], [26 ---#26 - 20B 32T Press], [28 ---#28 - 21 1/2 60T Press], [290 ---#290 - Horz 8'' Quench], [291 ---#291 - Vent 12'' Quench], [292 ---#292 - Horz 12'' Quench], [294 ---#294 - Horz 12'' Die Quench], [304 ---#304 - Horz 10'' Quench], [318 ---#318 - 250T Automatic], [319 ---#319 - 200T Aida], [32 ---#32 - 100T M-8 Press], [33 ---#33-Gardner Dbl Disc26''], [34 ---#34 Blanchard 42''], [343 ---#343 - 600T Verson], [346 ---#346 - 800T Verson], [446 ---#446 -VF3SS Vrt Mach Ctr], [54 ---#54-Gardner Db
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'.
I do not see a difference between the code I posted and your fix. Am I missing something ?
Sharath,
Not sure what you are asking. Below the value of @sql from my code:
Select ID_ITEM,Qty,Value, [1 ---#1 - 21 1/2 AC 60T Press], [101 ---#101 -Vibratory Bowl (20 c.f.)], [12 ---#12 - 60T Automatic], [139 ---#139 - Shear LG], [156 ---#156 - Parts Washer(28'' Wide)], [157 ---#157 - Parts Washer(22''Wide)], [17 ---#17 - 21 1/2 AC 60T Press], [195 ---#195-Bruderer Roll Lvler], [2 ---#2 - 110T Clearing], [21 ---#21 - 150T Minister Press], [22 ---#22 - Keyence Ink Jet Printer], [237 ---#237 Vibratory Bowl (15 c.f.)], [26 ---#26 - 20B 32T Press], [28 ---#28 - 21 1/2 60T Press], [290 ---#290 - Horz 8'' Quench], [291 ---#291 - Vent 12'' Quench], [292 ---#292 - Horz 12'' Quench], [294 ---#294 - Horz 12'' Die Quench], [304 ---#304 - Horz 10'' Quench], [318 ---#318 - 250T Automatic], [319 ---#319 - 200T Aida], [32 ---#32 - 100T M-8 Press], [33 ---#33-Gardner Dbl Disc26''], [34 ---#34 Blanchard 42''], [343 ---#343 - 600T Verson], [346 ---#346 - 800T Verson], [446 ---#446 -VF3SS Vrt Mach Ctr], [54 ---#54-Gardner Dbl Disc30''], [96 ---#96 - 600T Bliss]From dbo.Item_WC
PIVOT (Min(Cycle)
For WrkCtr IN ([1 ---#1 - 21 1/2 AC 60T Press], [101 ---#101 -Vibratory Bowl (20 c.f.)], [12 ---#12 - 60T Automatic], [139 ---#139 - Shear LG], [156 ---#156 - Parts Washer(28'' Wide)], [157 ---#157 - Parts Washer(22''Wide)], [17 ---#17 - 21 1/2 AC 60T Press], [195 ---#195-Bruderer Roll Lvler], [2 ---#2 - 110T Clearing], [21 ---#21 - 150T Minister Press], [22 ---#22 - Keyence Ink Jet Printer], [237 ---#237 Vibratory Bowl (15 c.f.)], [26 ---#26 - 20B 32T Press], [28 ---#28 - 21 1/2 60T Press], [290 ---#290 - Horz 8'' Quench], [291 ---#291 - Vent 12'' Quench], [292 ---#292 - Horz 12'' Quench], [294 ---#294 - Horz 12'' Die Quench], [304 ---#304 - Horz 10'' Quench], [318 ---#318 - 250T Automatic], [319 ---#319 - 200T Aida], [32 ---#32 - 100T M-8 Press], [33 ---#33-Gardner Dbl Disc26''], [34 ---#34 Blanchard 42''], [343 ---#343 - 600T Verson], [346 ---#346 - 800T Verson], [446 ---#446 -VF3SS Vrt Mach Ctr], [54 ---#54-Gardner Db
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'.
Can you post your exact query , Will fix it.
ASKER
Copy of exact query:
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;
Print @col
Set @sql =
N'Select ID_ITEM,Qty_Next12,Value_N ext12, ' + @col +
'From dbo.Item_WC
PIVOT (Min(Sum_Std)
For WrkCtr IN (' + @col + ')) As PivotTable';
Exec sp_executesql @sql;
Thanks
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;
Print @col
Set @sql =
N'Select ID_ITEM,Qty_Next12,Value_N
'From dbo.Item_WC
PIVOT (Min(Sum_Std)
For WrkCtr IN (' + @col + ')) As PivotTable';
Exec sp_executesql @sql;
Thanks
Also the output of PRINT @sql;
Also I think you need to change , thats why you are not getting all columns
Declare @sql As NVARCHAR(MAX);
Declare @col As NVARCHAR(MAX);
Also I think you need to change , thats why you are not getting all columns
Declare @sql As NVARCHAR(MAX);
Declare @col As NVARCHAR(MAX);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. That was the issue.
Can you print the @sql and post here?