• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

error in crosstable exec using ms sql 2008

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
goodk
Asked:
goodk
1 Solution
 
devlab2012Commented:
I think you are missing "@" with @pivot parameter. The following part of query:

convert(varchar(100), pivot)

shoud be:
convert(varchar(100), @pivot)
0
 
goodkAuthor Commented:
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
 
goodkAuthor Commented:
I am using ms sql 8 - is my error due to sql version? please help!! thanks
0
Industry Leaders: 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!

 
tigin44Commented:
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
 
dbaSQLCommented:
>>>
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
 
goodkAuthor Commented:
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
 
dbaSQLCommented:
What did your statement return?
0
 
dbaSQLCommented:
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
 
dbaSQLCommented:
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
 
goodkAuthor Commented:
thanks a lot,  the solution is complicated!!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now