Solved

Pivot Query Problem

Posted on 2016-10-10
9
42 Views
Last Modified: 2016-10-11
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;
0
Comment
Question by:StampIT
  • 5
  • 3
9 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41837605
There is no limit on the number of columns.
Can you print the @sql and post here?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41837796
Please find the fix below-

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;

Open in new window


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

Output..
I hope it helps.
0
 

Author Comment

by:StampIT
ID: 41838377
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'.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41838401
You query has a column name error, check this.

Check
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41838404
Can you post your exact query , Will fix it.
0
 

Author Comment

by:StampIT
ID: 41838553
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_Next12, ' + @col +
'From dbo.Item_WC
PIVOT (Min(Sum_Std)
For WrkCtr IN (' + @col + ')) As PivotTable';


Exec sp_executesql @sql;

Thanks
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41838577
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);
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41838583
Complete query to test.

Declare @sql As NVARCHAR(MAX); 
Declare @col As NVARCHAR(MAX);

SET @sql = ''
SET @col = ''


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_Next12, ' + @col +
'From dbo.Item_WC
PIVOT (Min(Sum_Std)
For WrkCtr IN (' + @col + ')) As PivotTable';


Exec sp_executesql @sql;

Open in new window

0
 

Author Closing Comment

by:StampIT
ID: 41838601
Thanks. That was the issue.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Data via Excel--performance issues 2 55
SQL Express connecting form remote error 26 7 45
Delete from table 6 47
How can i use WITH CTE for checking exist value? 3 34
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question