Solved

error in crosstable exec using ms sql 2008

Posted on 2011-02-25
12
491 Views
Last Modified: 2012-05-11
Msg 156, Level 15, State 1, Procedure crosstab, Line 23
Incorrect syntax near the keyword 'pivot'.
please help



http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
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
12 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 34980693
I think you are missing "@" with @pivot parameter. The following part of query:

convert(varchar(100), pivot)

shoud be:
convert(varchar(100), @pivot)
0
 

Author Comment

by:goodk
ID: 34984701
myTable=AceData has the following fields,

type, job, InCredit

I want to see type across, job down and InCredit accumulated.

so I ran the following,
EXECUTE crosstab 'select Type from AceData group by Type', 'sum(InCredit)','Job','AceData'

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

Author Comment

by:goodk
ID: 35003876
I am using ms sql 8 - is my error due to sql version? please help!! thanks
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 26

Expert Comment

by:tigin44
ID: 35016296
why not use the PIVOT i.e.

here in the [type1], [type2], [type3] ...
should be replaced with the actual type values.

SELECT job, [type1], [type2], [type3]
FROM (
		SELECT type, job, InCredit
		FROM AceData
	) AS P
	PIVOT
	(
		SUM(InCredit) FOR type IN ([type1], [type2], [type3]) 
	) AS PVT

Open in new window

0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35020220
>>>
I got the following error, please help - thanks
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'pivot'.
Msg 208, Level 16, State 0, Line 1
Invalid object name '##pivot'.
Msg 208, Level 16, State 0, Procedure crosstab, Line 23
Invalid object name '##pivot'.
>>>

I used the same code, and simulated exactly the error you are receiving.  The problem is in this first statement:

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')

'Pivot' is a reserved keyword.  You need to bracket it, like this:  
EXEC ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2')

That was the reason for the syntax error near keyword 'pivot'.  That is also the reason for the second error, 'Invalid object name '##pivot'.'  Your first statement didn't create it, so it is not there.  Fix the first statement with the bracket I've suggested, and try again.  (it worked for me)

0
 

Author Comment

by:goodk
ID: 35023256
Wow, that was helpful,
but execute results are not correct.

ok, table AceData looks like this,
 job,type,InCredit


now I wanted it look like this, jobs to go down, Types to go acroos and InCredit to add up
       Type1  Type2 ..
job1      33        44
job2      55        65

This did not work
EXECUTE crosstab 'select Job from AceData
group by Job', 'sum(InCredit)','type','AceData'
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35023360
What did your statement return?
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35023689
I'm not discounting the sqlteam link, but no matter what I did, I could not get around that ##pivot table.  Printing out the statements rather than executing them, this is my output:

   SELECT jobtype AS [pivot] INTO ##pivot FROM AceData WHERE 1=2
   INSERT INTO ##pivot SELECT DISTINCT jobtype FROM AceData WHERE jobtype Is Not Null
   Msg 208, Level 16, State 0, Line 24
   Invalid object name '##pivot'.

So, I tried something else.  See my logic within.  Hopefully I have interpretted your post accurately, and given the output you're looking for.  I can't remember exactly where I found this procedure, but I didn't originate this logic.  It works rather well, though.
--create table
CREATE TABLE AceData (job varchar(15),jobtype varchar(15),InCredit int)
INSERT AceData (job,jobtype,incredit)
select 'firstjob','firsttype',10
union
select 'firstjob','secondtype',100
union
select 'firstjob','thirdtype',5
union
select 'secondjob','firsttype',50
union
select 'secondjob','secondtype',110
union
select 'secondjob','thirdtype',12
union
select 'thirdjob','firsttype',99
union
select 'thirdjob','secondtype',11
union
select 'thirdjob','thirdtype',2

--view the data
select * from dbo.acedata

--new crosstab proc
IF OBJECT_ID('usp_crosstab','p')>0
DROP PROC dbo.usp_crosstab
GO
CREATE PROC dbo.usp_CrossTab
  @table       AS sysname,        -- Table to crosstab
  @onrows      AS nvarchar(128),  -- Grouping key values (on rows)
  @onrowsalias AS sysname = NULL, -- Alias for grouping column
  @oncols      AS nvarchar(128),  -- Destination columns (on columns)
  @sumcol      AS sysname = NULL  -- Data cells
AS
SET NOCOUNT ON;
/* 
exec dbo.usp_crosstab @table='AceData',@onrows = 'job',@oncols = 'jobtype',@sumcol = 'InCredit'
*/

set @oncols = @ONCOLS
--LISTING 4: Step 1 of the usp_CrossTab Stored Procedure: Beginning of the SQL String

DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @sql =
  'SELECT' + @NEWLINE + 
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS [' + @onrowsalias + ']'
    ELSE ''
  END

--LISTING 5: Step 2 of the usp_CrossTab Stored Procedure: Storing Keys in a Temp Table
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql = 
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table


--PRINT @keyssql + @NEWLINE -- For debug EXEC (@keyssql) 
EXEC (@keyssql)

--LISTING 6: Step 3 of the usp_CrossTab Stored procedure: Middle Part of SQL String
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST([' + @oncols +
                     '] AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '0'  -- NULL SET TO 0 NOT 1
                          ELSE @sumcol
                        END + @NEWLINE +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS [' + @key +']'
  
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

--LISTING 7: Step 4 of the usp_CrossTab Stored Procedure: End of SQL String
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

--PRINT @sql  + @NEWLINE -- For debug
EXEC (@sql)
GO



--run the proc
exec dbo.usp_crosstab @table='AceData',@onrows = 'job',@oncols = 'jobtype',@sumcol = 'InCredit'


--output
job       firsttype  secondtype    thirdtype
firstjob	   10	   100	         5
secondjob	   50	   110	        12
thirdjob	   99	    11	         2

Open in new window

0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 35023694
could be cleaned up a bit, perhaps with a @debug bit = 0 declaration, so you can avoid commenting out the PRINT/EXEC lines, but it works.  
0
 

Author Closing Comment

by:goodk
ID: 35053120
thanks a lot,  the solution is complicated!!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 29
SQL Replication question 9 41
TSQL query to generate xml 4 31
New to SSRS, extremely slow running report. 8 19
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

785 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