Solved

Pivot Query Problem

Posted on 2016-10-10
9
37 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 24

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
 
LVL 24

Expert Comment

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

Check
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 24

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 24

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 24

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Where not exists in same table 3 51
Getting the 3 middle digits 4 35
format nvarchar field as mm/dd/yyyy 4 61
Where clause in stored procedure 8 50
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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now