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

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
goodkAsked:
Who is Participating?
 
goodkAuthor Commented:
anybody - please  help!
0
 
Ephraim WangoyaCommented:

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
 
goodkAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Alpesh PatelAssistant ConsultantCommented:
one PIVOT syntax problem and main the objects (Table or fields you have used are not available)
0
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
goodkAuthor Commented:
acperkins: I would be interested in the response.  I was just getting frustrated that some experts mysteriously disappears for hours and days.



 thanks
0
 
Anthony PerkinsCommented:
>>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
 
goodkAuthor Commented:
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
 
goodkAuthor Commented:
no one want to help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.