[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I am using mssql 2008 express and when I run the following I am getting error at pivot

Posted on 2011-04-26
10
Medium Priority
?
434 Views
Last Modified: 2012-05-11
I saw the example at
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
but when i try to execute it I get erros.  Please help - thanks

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
0
Comment
Question by:goodk
10 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35471687

here

 
CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), @pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), @pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

Open in new window


I'm not really sure why you need this
convert(varchar(100), @pivot)
0
 

Author Comment

by:goodk
ID: 35471804
I tried
EXECUTE crosstab 'SELECT JOB FROM AceData) GROUP BY JOB', 'count(Job)', 'Year(Date)', 'Type'

I got the following error,
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'pivot'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Type'.
Msg 208, Level 16, State 0, Procedure crosstab, Line 23
Invalid object name '##pivot'.


my Table AceData has
Date, Job and Type as column names

Please help - thanks a lot

0
 

Accepted Solution

by:
goodk earned 0 total points
ID: 35472204
anybody - please  help!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35473316
one PIVOT syntax problem and main the objects (Table or fields you have used are not available)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35488859
>>no one want to help!!<<
You cannot use that SQL Script if you are still using SQL Server 2000 or the database compatibility is set to 80.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35488866
Ah, never mind I see that you are in fact using SQL Server 2000.

In any case you do not appear to be interested in the answer, so I will wish you good luck.
0
 

Author Comment

by:goodk
ID: 35494722
acperkins: I would be interested in the response.  I was just getting frustrated that some experts mysteriously disappears for hours and days.



 thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35497226
>>I was just getting frustrated that some experts mysteriously disappears for hours and days. <<
You do realize we are volunteers here, right?  We do have jobs and a personal life to attend to.

>>I would be interested in the response.<<
I suspect it is too late for that, your request to have the thread deleted made that moot and most members have probably moved on.  I would wait until this question is deleted and if you still need help, post a new question and this time be patient.  That is, if you want a solution.
0
 

Author Comment

by:goodk
ID: 35497911
acperkins:  I do appreciate all the experts and their time.  There may be a need to set "Off help desk" flag so other experts can assist and the customer may know that the chance of getting a response is not possible until certain time.

I will post the question later for consideration. thanks a lot.

0
 

Author Closing Comment

by:goodk
ID: 35499837
no one want to help!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 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