?
Solved

Pivot Query Problem

Posted on 2016-10-10
9
Medium Priority
?
61 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 29

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 29

Expert Comment

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

Check
0
 
LVL 29

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 29

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 29

Accepted Solution

by:
Pawan Kumar earned 2000 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore 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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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