Dynamic Cross Tab - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Dynamic Cross Tab

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2006-09-27 : 09:16:34

I have ran the dynamic cross tab below on SQL 2000
http://www.sqlteam.com/item.asp?ItemID=2955

but it shows me error, pls help

EXECUTE crosstab 'SELECT EmployeeID FROM Orders
GROUP BY EmployeeID', 'count(EmployeeID)', 'Year(OrderDate)', 'Orders'

Error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.
SELECT EmployeeID , [1996] = count(EmployeeID END), [1997] = count(EmployeeID END), [1998] = count(EmployeeID END) FROM Orders
GROUP BY EmployeeID

If it is that easy, everybody will be doing it

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-27 : 09:32:51

Are you sure you created the stored procedure exactly as it is in the article? When I run your query I get this SELECT generated

SELECT EmployeeID 
, '1996' = count( CASE Year(OrderDate) WHEN 1996 THEN EmployeeID END)
, '1997' = count( CASE Year(OrderDate) WHEN 1997 THEN EmployeeID END)
, '1998' = count( CASE Year(OrderDate) WHEN 1998 THEN EmployeeID END) 
FROM Orders 
GROUP BY EmployeeID

and this result

EmployeeID  1996        1997        1998        
----------- ----------- ----------- ----------- 
7           11          36          25
6           15          33          19
5           11          18          13
4           31          81          44
3           18          71          38
2           16          41          39
9           5           19          19
1           26          55          42
8           19          54          31

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 09:35:50

Exchange COUNT(EmployeeID) with another COUNT that will do the same thing.

COUNT(OrderID)

or similar.

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-27 : 09:38:59

This is the statement that is failing
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

And I guess the only way that can happen without an error is if ##pivot is empty.
That would happen if
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

Didn't padd any rows.
Does the Orders table have any rows (where OrderDate isn't null).

Also it uses a global temp table so can only be used from one spid at a time.

umm - maybe not. That would end up with a null string to execute.
Next though is @delim isn't being set.

Try adding selects to the sp to view values to see what is going wrong.
And make sure you don't have an ##pivot table before running it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-27 : 10:17:08

Here's one that doesn't have the global temp table problem (but may have others).
And will also work for v2005 - pivot is a reserved word - just means putting [] round the col name - or could change the name.
All I've done is put everything in a single string to execute.


CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS
declare @sql varchar(8000)
declare @spid varchar(20)
select @spid = convert(varchar(20),@@spid)
select @sql = 'declare @select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100)
select @select = ''' + @select + ''', @sumfunc = ''' + @sumfunc + ''', @pivot = ''' + @pivot + ''', @table = ''' + @table + '''
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SELECT ' + @pivot + ' AS [pivot] INTO #pivot' + @spid + ' FROM ' + @table + ' WHERE 1=2
INSERT INTO #pivot' + @spid + ' 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 like ''#pivot' + @spid + '%'' 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' + @spid + '

DROP TABLE #pivot' + @spid + '

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex('' FROM '', @select)+1, 0, '', '' + @sql + '' '')

EXEC (@select)
SET ANSI_WARNINGS ON'
exec (@sql)
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-28 : 13:32:40

Showed the proc to someone at work who got excited and tried to use it.
He got the same error.
It is because the global temp table is created owner dbo. The information schema views only give info about objects owned by the user so @delim isn't set.
There is no need for this though as characters are implicitly converted to numeric so if you replace the select @delim = .....
by
select @delim = ''''
it should all work.

Could also be associated with the SP being owned by the user not dbo.

I don't think my simpler version above would have that problem but I'm sa and can't be bothered to create a user to test it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

marcus65
Starting Member

2 Posts

Posted - 2006-11-17 : 10:08:23

I am having the same problem. I receive the error:

Incorrect syntax near the keyword ‘END’

when I execute the crosstab procedure in Query Analyzer, it runs just fine. when I try to set up the EXECUTE in a stored procedure, and call that stored procedure from a web page, I get the error. Or, if I run the EXECUTE query from my web page directly, I also get the error.

Help!!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-20 : 06:58:29

Help? re-read the entire post. I am sure Nigel has already told you why it does not work.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

marcus65
Starting Member

2 Posts

Posted - 2006-11-20 : 09:51:05

Thanks, DonAtWork. Had I been able to figure out the problem from re-reading the threads I wouldn't have posted the question.

I really appreciate the advice, though. Makes me want to be sure to seek help here again.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-20 : 13:46:27

Here. The post RIGHT ABOVE yours.

Showed the proc to someone at work who got excited and tried to use it.
He got the same error.
It is because the global temp table is created owner dbo. The information schema views only give info about objects owned by the user so @delim isn't set.
There is no need for this though as characters are implicitly converted to numeric so if you replace the select @delim = .....
by
select @delim = ''''
it should all work.

Could also be associated with the SP being owned by the user not dbo.

I don't think my simpler version above would have that problem but I'm sa and can't be bothered to create a user to test it.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources