Solved

Pivot Query Problem

Posted on 2016-10-10
9
52 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 41

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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