Solved

error in crosstable exec using ms sql 2008

Posted on 2011-02-25
12
483 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now