question re robvolks awesome pivot table code

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

SQL Server 2000 Forums

Transact-SQL (2000)

question re robvolks awesome pivot table code

Author

Topic

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-04-30 : 11:55:59

robvolks crosstab/pivot sproc is a pretty cool.

question:

I am trying to use the pivot sproc (aka crosstab) in another sproc. In that other sproc, I want to create a list of the info to be pivoted, then pivot that info into a temp table, and then return the pivoted info to the user who called the sproc.

in http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=16225 rob mentions that you can store the results in a table by adding a simple 'INTO xxx'

however, this doesnt work for situations where there are multiple users running the code as it creates a table in the database (ie multiple users trying to create/use the same table).

This doesnt seem to work because temp tables created via EXEC are not in the same scope as the transaction/sproc.

So, is there any way for the pivot results to get stored in a temporary table??

Anyone have some ideas??

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-30 : 16:24:51

An idea:
Create global temporary tables, which are in the same scope.
Name the tables with numbers, e.g. ##1.
Before you create the temporary table, get the next available number by running a loop to see which one doesn't exist.


DECLARE @tblNum INT,@TblName varchar(25)
SET @tblnum = 1
WHILE @tblNum < 1000 --some large number to try the loop up to
BEGIN
SET @tblName = 'tempdb..##' + ltrim(str(@tblnum))
IF OBJECT_ID(@name) IS NULL --this table name isn't used yet
BREAK
ELSE
SET @tblNum = @tblNum + 1

--Here have the code for:
1. If @tblNum = 1000, we broke b/c we exceeded the highest number
2. create the temp table with the latest value in @tblName

Sarah Berger MCSD

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-04-30 : 16:31:08

thought about that but not sure I like global temp tables in general (kinda like having global variables in your code).

did a quick and dirty hack to create a table dynamically(ie creating the field names based on what the crosstab sproc would return) using a cursor but its a bit ugly

while I got around the limitation, would still be nice to be able to get the crosstab sproc to use temp tables.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-30 : 20:14:57

The way to use regular temp tables it is to have the ENTIRE procedure executed as dynamic SQL. That eliminates the scope problems that required the global temp tables. Unfortunately, it also makes assembling the SQL a lot more difficult.

Check the comments section on the article, there are several variations on the code that were posted, one of them will probably do what you want, or point you in the right direction.

gdeconto
Posting Yak Master

107 Posts

Posted - 2003-05-01 : 00:04:38

Thx for the info Rob.

Appreciate the help and the original crosstab code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources