Please start any new threads on our new site at 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 

Posting Yak Master

107 Posts

Posted - 2003-04-30 : 11:55:59
robvolks crosstab/pivot sproc is a pretty cool.


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 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??

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
SET @tblName = 'tempdb..##' + ltrim(str(@tblnum))
IF OBJECT_ID(@name) IS NULL --this table name isn't used yet
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
Go to Top of Page

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.

Go to Top of Page

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.

Go to Top of Page

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.

Go to Top of Page

- Advertisement -