We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
455 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
Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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)

Author

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

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
one PIVOT syntax problem and main the objects (Table or fields you have used are not available)
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

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



 thanks
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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.

Author

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.

Author

Commented:
no one want to help!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.