Solved

Pivot Query Problem

Posted on 2016-10-10
9
47 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
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.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 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