Article: Dynamic Cross-Tabs/Pivot Tables

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

Site Related Forums

Article Discussion

Article: Dynamic Cross-Tabs/Pivot Tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-03-02 : 20:41:33

IMHO, the best feature of MS Access is the TRANSFORM statement, used to create cross-tabs/pivot tables. It does all of the work of dynamically generating the cross-tabulation and the summary calculations. T-SQL unfortunately doesn't have this statement, so you're stuck using complicated SQL commands, expensive 3rd party products, or exotic OLAP to make pivot tables...or you can use the following procedure to dynamically create them!

Article Link.

sommai
Starting Member

3 Posts

Posted - 2001-12-02 : 21:40:42

Feel sad to know limitation of version 6.5. I was known that new latest version have a lot of feature and performance but I still trap in 6.5 because I have an application which only run with this version. I wonder that CASE keyword could run on 6.5??

tony.holm
Starting Member

2 Posts

Posted - 2002-01-18 : 13:24:30

Who has the final working code that fixes the "Incorrect syntax near the keyword 'END'" error?

robvolk's code worked fine in QA, but fails with the "Incorrect syntax near the keyword 'END' error in an Active Server Page.

Haven't tried cwburke's code yet, wanted to know first if it fixes problem. My query string is only about 50 char long, not 8000 like Burke.

Help please.........

tony.holm
Starting Member

2 Posts

Posted - 2002-01-23 : 12:52:32

Found one that works in ASP and does not give this error.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608#

quote:

I know this is a VB problem but,

Any ideas on why this would give the following error when called in VB:

Incorrect Syntax Near Keyword 'END'.

It doesn't seem to like the statement syntax of Count(Case @pivot When ?? Then ?? END). Is there something I could do different? It works fine through query analyzer.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-23 : 13:05:56

Thanks for the link Tony, sorry no one got an answer for you earlier.

By any chance, does the data you're cross-tabbing contain embedded apostrophes? That could very well throw an error since the code I wrote relies on dynamic SQL strings, and an apostrophe in the data would screw it up. Please let me know if that's true, I'd like to update the code to provide more flexibility and correct errors such as this.

Thanks.

hoelo
Starting Member

1 Post

Posted - 2002-01-29 : 17:52:26

Hey guys..

Rob..great SP.. I hacked it apart and made my own b/c I had to do some fanciness to it (including adding the where clause to the pivot'd column before I saw you had added it.. Yours put me on the right path, for which I am very thankful..

Couple of things people have asked:

1. Global table.. This REALLY isn't a big deal guys.. I know its somewhat kludgy but if you're worried about multi-user capability, just do one of the following:
A. Wrap the entire contents of the stored procedure within a transaction. B/c of the atomicity of transactions, this will prevent any other users from blowing up your pivot table or such.
B. This is what I like to do although it requires slightly more work.. Instead of referring to your pivot table as ##pivot, generate a random number and concatenate that onto ##pivot, sticking the resultant name in a variable which you then use throughout the SP where you would use '##pivot'.
Since so little work is actually done on the pivot table itself (if your SP is like mine, the bulk of the work is in running the query, not generating it), the odds are extremely unlikely that two users will happen to run the SP at the same time AND that both will get the same random number. In fact, if you want to be doubly-sure.. Generate the random number, test for the existence of the table. If it fails the check.. great.. If not, generate a different random number.

2. The 'invalid syntax before END' question.. I got this when, as Rob mentioned, there was a ' embedded where there shouldn't have been. This also happened to me once when my pivot column had an empty string value.. The pivot query catches NULLs but not empty strings. Depending on your type of data this could cause a problem.

I'm not positive that's what I did on #2 but I think those were the two cases that gave me problems. I know I had that error and fixed it. One thing that is EXTREMELY helpful in debugging this is to change the 'exec (@select)' at the end to 'select (@select)'.. This will give you back the query itself that you generated which you can then copy/paste to another QA window and see where it went wrong.

This is my signature.
There are many like it
but this one is mine..
(etc)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-29 : 18:39:54

Oh boy, you're paraphrasing Full Metal Jacket. You DON'T want to get me going on Full Metal Jacket! I love that movie! Unfortunately there aren't any flattering quotes from it with which to respond to you.

Thanks for the tips on improving the SP! (I did get your email BTW, stoooooopid service dumped it into a junk mail folder and it got dumped before I could reply...and I agree, Chasey Lain is mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm)

You could also construct the ENTIRE SP to use dynamic SQL, that would let you use a normal temp table, but I gave up trying to figure out the single quote issues that arose. Adding the timestamp or some random number would work, but it involves more dynamic SQL. The real issue is that there is a mix of dynamic and fixed SQL in the procedure, and changing the existing balance seems to cause more problems that it's worth.

If you've modified it and fixed existing problems, please feel free to post them in this thread (like cwburke and GreatInca). Thanks again!

You will not laugh! You will not cry! You will learn by the numbers! I will teach you!

Edited by - robvolk on 01/29/2002 18:41:29

jcoahran
Starting Member

1 Post

Posted - 2002-02-08 : 15:38:01

Has anyone written a version of this proc can handle multiple users? I've attempted it but with no luck. Any help would be appreciated.

Thanks,
Joe

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-08 : 15:50:07

Yeah, go to page 2 of these replies, GreatInca and cwburke have some code that don't use global temp tables.

WebPartz
Starting Member

1 Post

Posted - 2002-04-08 : 13:38:00

This code looks amazing, but I can't get it to work. I think my problem is even simmplier than what you solve though

e.g. Lets say I have a table that tells me what brokers deal in what stocks.

Broker Name:
Stock Symbol:

I want 'Broker Name' down the left, 'Stock Symbol' Across the top, and in the middle I want a "1" to confirm that they do deal in that stock

EXECUTE crosstab 'select broker from tbl_broker group by broker, 'count(symbol)','stock_symbol','tbl_broker'

get errors like
Line 1: Incorrect syntax near 'THE'.

Using SQL2000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 14:00:33

In all likelihood, the number of pivot columns causes the dynamic SQL statement to exceed 8000 characters. If you're pivoting more than 300-350 stock symbols, that's definitely the problem. See if you can limit it to 20 or less symbols, just to check that the syntax is correct...use the following:

SELECT DISTINCT TOP 20 stock_symbol INTO temp_symbol FROM tbl_broker

EXECUTE crosstab 'select broker from tbl_broker group by broker', 'count(symbol)','stock_symbol','temp_symbol'

DROP TABLE temp_symbol

If it DOESN'T work, then there might be problems with the stock symbol data (embedded spaces or apostrophes are usually the culprits), or an obscure syntax error in the code. Instead of EXEC (@select), try PRINT @select and look at the SQL statement generated. If you paste that into a query analyzer window and run it, it'll help pinpoint the error more closely.

If if DOES work, then at least the syntax is correct, but in all likelihood the code won't let you pivot all of your stocks. You'd have to limit the number of pivot columns you return in a single execution. I have an idea on somehow combining multiple pivot sets into one, but I don't know if it will work, and it's SO DAMN MESSY that it's really not worth it.

abdenn75
Starting Member

1 Post

Posted - 2002-05-20 : 13:10:12

Hi Guys,
I can't use a global temp table but I got different idea. Instead of creating a temp table for each user, use one table and add a unique key that identifies that user. In my case I added the sessionId.
Here is the code :

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100) ,
@where varchar(1000)='1=1' ,
@delim varchar(1),
@sessionId 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 PivotTemp (SessionId, Pivot) SELECT DISTINCT ' +@sessionId+','+ @pivot + ' FROM ' + @table + ' WHERE '
+ @where+ ' AND ' + @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
--SELECT @delim=''
--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 PivotTemp WHERE SessionId = @sessionId

DELETE FROM PivotTemp WHERE SessionId = @sessionId

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

PRINT @select
EXEC (@select)
SET ANSI_WARNINGS ON
GO

mikeosmith
Starting Member

1 Post

Posted - 2002-07-09 : 16:02:52

I have been looking for this very thing. It works in Access (with the TRANSFORM, very handy). I'm having trouble though. When I run:

EXECUTE crosstab 'SELECT part_name FROM tbl_parts INNER JOIN tbl_stock ON (tbl_stock.part_name=tbl_parts.part_name) GROUP BY tbl_parts.part_name','sum(tbl_stock.qty)','tbl_stock.finish','tbl_stock'

I get:
Server: Msg 1038, Level 15, State 2, Line 1
Cannot use empty object or column names. Use a single space if necessary.

Any help would be greatly appreciated

Corobori
Posting Yak Master

105 Posts

Posted - 2002-07-12 : 12:24:29

I am experiencing some difficulties running this proc on a SQL Server 2000. Originally my problem was due to the collation which is SQL_Latin1_General_CP1_CS_AS on my machine. Because of the case sensitive I had to change some names such as information_schema to INFORMATION_SCHEMA and a couple of others.

Trying to run robvolk's sample against the pub database (EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'sum(qty)','stor_id','stores') I get this message:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

I am just a step above "absolute beginner" so I am a bit stuck

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-12 : 12:34:05

How about some of the other samples? Any luck with those?

It's hard for me to determine where the problem might be because I've never used a case-sensitive server. I don't know if that will effect how this code functions.

One thing you might try is to replace the EXEC (@select) line with PRINT @select. This will display the generated SQL. You can then copy and paste it into Query Analyzer and run it; the error message should better pinpoint exactly where the problem is.

Things to look for in the SQL statement: column or table names with spaces or other non-alphanumeric characters, embedded apostrophes, quotes, commas and periods in the data.

If you have MS Access available, try linking the SQL Server table to it and use the Access crosstab function to see if it can create the same crosstab. It should at least help you track down whether data values are causing the problem or not.

Johann Von Satan
Starting Member

1 Post

Posted - 2002-08-09 : 19:56:24

I created a version of this stored procedure that uses the global table but will generate a global table using a GUID (stripped of the dashes). I also created a temporary table called #parm. this is because I realized that we cannot use local variables in dynamically generated queries, but you can use temp tables. So I joined the #parm table with the pivot query to retrieve. Also, I pass strings from the dynamic query through temp table #strs.
Very kludgy no?

CREATE PROCEDURE usp_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

CREATE TABLE #strs (s VARCHAR(8000) )

CREATE TABLE #parm
(
[select] varchar(7700),
[sumfunc] varchar(100),
[pivot] varchar(100),
[table] varchar(100),
[delim] VARCHAR(1)
)
INSERT INTO #parm ( [select], [sumfunc], [pivot], [table], [delim] ) VALUES ( @select, stuff(@sumfunc, len(@sumfunc), 1, ' END)' ), @pivot, @table, '' )

DECLARE @tblname AS VARCHAR(255)
SET @tblname = '##' + REPLACE(CONVERT( VARCHAR(255), NEWID()), '-', '')

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ' + @tblname + ' FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO [' + @tblname + '] 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= @tblname AND column_name='pivot'

UPDATE #parm SET delim = @delim

DECLARE @nSQL VARCHAR(8000)
SET @nSQL = 'DECLARE @sql VARCHAR(8000) SET @sql = '''' '
SET @nSQL = @nSQL + 'SELECT @sql=@sql + '''''''' + convert(varchar(100), ' + @tblname + '.pivot) + '''''' = '' + '
SET @nSQL = @nSQL + 'stuff(sumfunc,charindex( ''('', sumfunc )+1, 0, '' CASE '' + #parm.pivot + '' WHEN '' '
SET @nSQL = @nSQL + '+ delim + convert(varchar(100), ' + @tblname + '.pivot) + #parm.delim + '' THEN '' ) + '','' FROM ' + @tblname + ', #parm '
SET @nSQL = @nSQL + ' INSERT INTO #strs ( s ) VALUES( @sql ) '

EXEC( @nSQL )

EXEC ('DROP TABLE ' + @tblname)

SELECT @sql = s FROM #strs

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

EXEC (@select)
SET ANSI_WARNINGS ON
GO

steve_borkowski
Starting Member

2 Posts

Posted - 2003-01-15 : 16:23:21

quote:

The string problem wasn't discovered until today, after I uploaded some more data into the database. Before it would work in QA, but not after I loaded the data. I still think there is a problem w/ VB and the way it calls it somehow. Anywho, I'll see if I can't work toward that case of beer!!!!

Thanks,

Chris

Hack Master

steve_borkowski
Starting Member

2 Posts

Posted - 2003-01-16 : 16:39:18

quote:

Great, simple article, but has anyone solved the problem of using a global temp table within the procedure? Unless this is solved, multiple users could have problems suing this script. Anyone want to share an updated version of this script?

I've done my best with this for functionality, but the procedure is somewhat inelegant, requires many parameters, and has some peculiarities, but it will generate a query around 80,0000 characters long, resolves the hardcoded global temp table, and adds an output table property to it.

It works for me.

Some things to remember; this procedure is sensitive to the usage of the parameters.
All parameters must be submitted. Only two can be blank. (INTO and CROSS-TAB SORT)
All parameters with the exception of COLUMN HEADING and COLUMN PREFIX must have a space at the end.
If you use the procedure to output data to a table on the server, you must make sure the table doesn't already exist.
The procedure DOES NOT drop the output prior to execution.

By the way, just for fun I placed a statement in an MS Access Passthrough query and it worked flawlessly. I was also able to use it in MS Query to pull data into MS Excel.

Here's the sample statement I used...

execute sp_crosstab
'SELECT City, ContactTitle, ', -- select
'', -- into
'FROM CUSTOMERS ', -- from
' ', -- where
'GROUP BY City, ContactTitle ', -- group by
'ORDER BY City, ContactTitle ', -- order by
'count(CustomerID)', -- aggregation
'Country', -- pivot column
'Total_Contacts_In_', -- column heading prefix
'(SELECT Country FROM CUSTOMERS) a', -- source select
'Country ', -- source column
'DESC ' -- column heading sort

-Steve Borkowski

CREATE PROCEDURE sp_crosstab
@select varchar(8000),
@output varchar(100),
@from varchar(8000),
@where varchar(8000),
@group varchar(8000),
@sort varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@colpref varchar(50),
@table varchar(100),
@sourcecol varchar(100),
@tabsort varchar(5)

AS

-- Declarations
DECLARE @sql varchar(8000),
@sql1 varchar(8000),
@sql2 varchar(8000),
@sql3 varchar(8000),
@sql4 varchar(8000),
@sql5 varchar(8000),
@sql6 varchar(8000),
@sql7 varchar(8000),
@sql8 varchar(8000),
@sql9 varchar(8000),
@delim varchar(1),
@sqllen numeric(4),
@sqlnum numeric(10),
@pvalue varchar(100),
@pivtab varchar(100)

-- Turn off
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

set @pivtab = '##pivot'+RTRIM(CONVERT(CHAR(30),@@CPU_BUSY))+@colpref+@pivot

-- Create row heading list
-- Start by creating the table
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ' + @pivtab + ' FROM ' + @table + ' WHERE 1=2')

--Now insert the rows into it
EXEC ('INSERT INTO ' + @pivtab + ' SELECT DISTINCT ' + @pivot +
' FROM ' + @table +
' WHERE ' + @pivot + ' Is Not Null ' +
'ORDER BY ' + @sourcecol + ' ' + @tabsort)

-- Setup the intial SQL query
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= @pivtab AND column_name='pivot'

-- Create the Case statement section of the query
exec ('Declare cur_pivot_cursor scroll CURSOR FOR select PIVOT from '+ @pivtab )

OPEN cur_pivot_cursor

FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

set @sqlnum = 1

WHILE @@FETCH_STATUS = 0

BEGIN

-- Run the iteration of column headings
set @sql=@sql + '''' + @colpref + convert(varchar(100), @pvalue) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, 'CASE ' + @pivot +
' WHEN ' + @delim + convert(varchar(100), @pvalue) + @delim + ' THEN ' ) + ', '

-- Measure the length of the string
set @sqllen = len(@sql)

-- Check to see if we are running long on the SQL text
-- Set first division
if @sqllen > 7800 and @sqlnum = 1
begin
set @sql1 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

-- Set second division
if @sqllen > 7800 and @sqlnum = 2
begin
set @sql2 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

-- Set third division
if @sqllen > 7800 and @sqlnum = 3
begin
set @sql3 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

-- Set fourth division
if @sqllen > 7800 and @sqlnum = 4
begin
set @sql4 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

-- Set fifth division
if @sqllen > 7800 and @sqlnum = 5
begin
set @sql5 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

-- Set sixth division
if @sqllen > 7800 and @sqlnum = 6
begin
set @sql6 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

-- Set seventh division
if @sqllen > 7800 and @sqlnum = 7
begin
set @sql7 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0

end

-- Set eighth division
if @sqllen > 7800 and @sqlnum = 8
begin
set @sql8 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

-- Set nineth division
if @sqllen > 7800 and @sqlnum = 9
begin
set @sql9 = @sql
set @sqlnum = @sqlnum + 1
set @sql = ''
set @sqllen = 0
end

--GET NEXT RECORD
FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

END

-- Now that we are done with the cursor, close it and give the memory back
CLOSE cur_pivot_cursor
DEALLOCATE cur_pivot_cursor

-- Go ahead and drop the temporary table that contained the column headings
EXEC ('DROP TABLE ' + @pivtab)

-- Snatch off the final comma left at the end of the built string
SELECT @sql=left(@sql, len(@sql)-1)

-- Execute the query string built by the iterative process
exec (@select + @sql1 + @sql2 + @sql3 + @sql4 + @sql5 + @sql6 + @sql7 + @sql8 + @sql9 + @sql + @output + @from + @where + @group + @sort)

-- Set the ANSI Warnings switch back the way we found it.
SET ANSI_WARNINGS ON

GO

Hack Master

edwardch
Starting Member

8 Posts

Posted - 2003-03-04 : 16:19:47

Problem!!
I've been trying to solve this for hours now.. I've tried pretty much all the crosstab procedures I could find on the web, but none of them seem to work on this.... I have 2 tables
incident - table name
incident_id incident_type_id
1 1
2 2
3 2
4 1
5 3
6 4
7 5
8 1
type - table name
type_id type_desc
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
7 test7
8 test8
9 test9
10 test10

the ideal result should be
test1 test2 test3 test4 test5
reported 3 2 1 1 1

I tried to do this but it never returns anything, tried so many ways.

EXECUTE crosstab 'select type_desc as reported from type inner join incident on (type.type_id=incident.incident_type_id)
group by type_desc', 'count(incident_type_id)','type_desc','type'

help deeply appreciated!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-04 : 17:00:28

Works for me.

DROP TABLE Incident
CREATE TABLE Incident
(
incident_id int,
incident_type_id int
)

DROP TABLE Type
CREATE TABLE Type
(
type_id int,
type_desc nvarchar(10)
)

INSERT INTO Incident(incident_id, incident_type_id)
SELECT 1,1 UNION
SELECT 2,2 UNION
SELECT 3,2 UNION
SELECT 4,1 UNION
SELECT 5,3 UNION
SELECT 6,4 UNION
SELECT 7,5 UNION
SELECT 8,1

INSERT INTO Type(type_id, type_desc)
SELECT 1, 'test1' UNION
SELECT 2, 'test2' UNION
SELECT 3, 'test3' UNION
SELECT 4, 'test4' UNION
SELECT 5, 'test5' UNION
SELECT 6, 'test6' UNION
SELECT 7, 'test7' UNION
SELECT 8, 'test8' UNION
SELECT 9, 'test9' UNION
SELECT 10, 'test10'

EXECUTE crosstab 'select type_desc as reported from type inner join incident on (type.type_id=incident.incident_type_id)
group by type_desc', 'count(incident_type_id)','type_desc','type'

edwardch
Starting Member

8 Posts

Posted - 2003-03-04 : 17:46:15

but when I did this query on my sql server with existing tables which contains quite a lot of information....

EXECUTE crosstab 'select type_description as reported from type inner join incident on (type.type_id=incident.incid_type_id)
group by type_description','count(incid_type_id)','type_description','type'

but returns nothing... no rows at all..., tried so many things, but still returns nothing...
which crosstab proc did you use? coz I used the one below created by
cwburke.

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

/* Original Script Written By Rob Volk @ SQLTeam.com*/

DECLARE @sql varchar(8000), @delim varchar(1)
--Added to increase the size of the query string--cwburke 10/09/01
DECLARE @sql2 varchar(8000) --Second "pivot" sql string
DECLARE @BeginSelect varchar(8000) --Initial Select statement passed in by user
DECLARE @EndSelect varchar(8000) --Initial From statement passed in by user
DECLARE @LastPivot varchar(100) --Last pivot read from ##pivot before Len(@sql) > 8000
DECLARE @Revsql varchar(8000) --@sql Reversed
DECLARE @x int --Charindex of "WHEN" in @RevSql
--End 10/09/01

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 Order By ' + @pivot) --Add Order By Clause Here

SELECT @sql='', @sql2='', @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 ' ) + ' , ' + char(13)
FROM ##pivot

--If @sql > 8000, then build 2nd query string
If Len(@sql) >= 8000
Begin
--Reverse the string
Set @RevSql=Reverse(@sql)

--Search for last occurence of WHEN
Set @x = charindex(',', @RevSql)

--Reverse the string again
Set @LastPivot = Reverse(Left(@RevSql, @x -1))

--Reset @sql to remove the imcomplete item
Set @sql = Left(@Sql, Len(@sql) - (@x - 1))

--Get the last pivot
Set @LastPivot = Substring(@LastPivot, 4, charindex("' ", @LastPivot) - 4)

--Pickup From Last read pivot and build @sql2
Select @sql2 = @sql2 + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0,
'CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ' , ' + char(13)
FROM ##pivot
WHERE pivot >= @LastPivot
End

DROP TABLE ##pivot

--If @sql2 exists, remove trailing comma
If len(@sql2) > 0
Begin
SELECT @sql2=left(@sql2, len(@sql2)-4)
End
Else
Begin
--Remove trailing comma from @sql if @sql2 is empty
Select @sql=left(@sql, len(@sql)-1)
End

--Get Initial Select
Select @BeginSelect=Left(@select, charindex(' FROM ', @select)-1) + ', '
--Get Initial From
Select @EndSelect=Right(@select, Len(@select) - CharIndex(' FROM ', @select)+1)

--EXEC (@BeginSelect + @sql + @sql2 + ' ' + @EndSelect)
SET NOCOUNT ON
SET ANSI_WARNINGS ON

Edited by - edwardch on 03/04/2003 17:48:05

Edited by - edwardch on 03/04/2003 17:54:12

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources