SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Dynamic Cross-Tabs/Pivot Tables
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 12

Vorbis
Starting Member

South Africa
6 Posts

Posted - 10/10/2001 :  08:57:48  Show Profile  Reply with Quote
Good publication, alot of people do ask for this, but bad implementation.

The use of the global temporary table makes it not usable from multiple instances.
If anyone will like to have solution which can be used on a single DB by multiple users simultaniously - e-mail me then I will write it and post it here or will e-mail it back.

I'm asking is there anyone who may need it because I know myslef. if I have to do it for myslef it will be long time before I do it, but for someone else - it is always a pleasure.

TS
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 10/10/2001 :  15:04:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
Vorbis-

Just post it if you've got it. Why wait for e-mail?

Bad implementation??? Well, then, I'll just delete the whole article then!

Go to Top of Page

GreatInca
Posting Yak Master

USA
102 Posts

Posted - 10/10/2001 :  17:50:29  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
Here's a version that does not use temp tables -- mad good for the concurrency. The version I pasted from article does not isnert case statement correctly. This one does less string handling becuase the aggrage parameter is plit into the aggrate function and the expression for the aggragate. Don't know if I preserved the 'order by the pivot column' functionality
but everything else is preserved.

You can omit the @Query parameter and it will give you just the case statement assuming that the pivotcol and the data for the aggragate can be related together somehow. You can also provide a @query parameter and set the @run parameter to 0 and it will print the generated to paste query instead of executing it (so the case stement generation doesn't have to be run again incase it gets expensive on poorly designed DBs).

If you want to see the interim strings that are generated, uncomment the 2 prints near the bottom.

ALTER Procedure CrossTab
@AggFunction Char(3),
@AggCol varchar(100),
@PivotCol varchar(100),
@PivotColTable varchar(100),
@Query VarChar(8000)='',
@Run bit=1
AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @Divider VarChar(100)
DECLARE @CaseTemplate varchar(8000)
DECLARE @Execute varchar(8000)
DECLARE @CRLF Char(2)
SET @Divider='**********************************************************************'
SET @CRLF=Char(13)+Char(10)

--Generate Case Statement template
SET @CaseTemplate='''ISNULL(' + @AggFunction + '(CASE ' + @PivotCol + ' WHEN '''''' + CONVERT(VarChar(8000),' + @PivotCol + ') + '''''' THEN ' + @AggCol + ' END),0) AS ['' + CONVERT(VarChar(8000),' + @PivotCol + ') + ''],'''

--Generate Case statement
SET @Execute='DECLARE @SQL VarChar(8000)' + @CRLF + 'DECLARE @CRLF Char(2)' + @CRLF
SET @Execute=@Execute+'SET @CRLF=Char(13)+Char(10)' + @CRLF + 'SET @SQL=''''' + @CRLF + @CRLF
SET @Execute=@Execute+'SELECT @SQL=@SQL+' + @CaseTemplate + ' + @CRLF' + @CRLF + 'FROM ' + @PivotColTable + ' ORDER BY ' + @PivotCol+ @CRLF + @CRLF

--Run Query or output query or case statement
IF @Query=''
BEGIN
SET @Execute=@Execute+'PRINT LEFT(@SQL, LEN(@SQL)-3)' + @CRLF + @CRLF
END
ELSE
BEGIN
SET @Execute=@Execute+'SET @SQL=REPLACE(''' + @Query + ''', ''FROM'', '', '' + @CRLF + LEFT(@SQL, LEN(@SQL)-3) + @CRLF + ''FROM'')' + @CRLF + @CRLF
IF @Run=1
BEGIN
SET @Execute=@Execute+'EXECUTE (@SQL)'
END
ELSE
BEGIN
SET @Execute=@Execute+'PRINT @SQL' + @CRLF + @CRLF
END
END

--PRINT @CaseTemplate + @CRLF + @Divider
--PRINT @Execute + @CRLF + @CRLF + @Divider

EXECUTE (@Execute)


Go to Top of Page

Vorbis
Starting Member

South Africa
6 Posts

Posted - 10/11/2001 :  03:05:01  Show Profile  Reply with Quote
quote:

Vorbis-

Just post it if you've got it. Why wait for e-mail?

Bad implementation??? Well, then, I'll just delete the whole article then!



Go to Top of Page

Vorbis
Starting Member

South Africa
6 Posts

Posted - 10/11/2001 :  04:20:09  Show Profile  Reply with Quote
Ups, sorry for my bad choice of words. I asked is there anyone who needs such script because I don’t have it. I will write it if it will make any good to someone.
The posting from ‘GreatInca’ is much better, but it has the problem to fail if there are too many records to pivot.

The big question is not to bypass the temporary tables, but to make it working (as illustration how to deal with the temp tables in general in concurrent environment).

I thought that this is place to discuss and ‘throw stones’ against any script in order to expand and improve our coding style and technique.

I’ll try to make ‘productional’ style of script although I can’t promises when it will be published but it will be. Just give me a day or two to find some time.
I love to resolve SQL tasks for pleasure, so I will enjoy this.
Till later.
TS
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/11/2001 :  04:36:37  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi Vorbis

It is a place to discuss scripts, but you could be a little nicer about it. Most of the authors here write articles with a view of something that can be explained. I know with some of mine, I have made it simple, then explained some of the ways it can be made better, faster or more robust. But to add all that code in would have made it too hard to explain properly.

This code, has been used by, and worked perfectly for a lot of people (to give you some idea, it has been read 7254 times), the fact that it doesn't meet someones particular problem isn't really anyones fault, the article was to demonstrate a technique, which it did.

That being said, why not use Rob's script, but name your global temp table something unique for every user. Add the SPID to the name or something.



Damian
Go to Top of Page

sommai
Starting Member

3 Posts

Posted - 11/02/2001 :  03:16:12  Show Profile  Reply with Quote
Hello,
Did someone still live with MS SQL 6.5? I could not run this script. first error is varchar size can not more than 255. other is no left function. the last is no tempdb.information.schema. Other question if I need to place only data in pivot table without count or sum function; how to do that??

Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 11/02/2001 :  08:41:35  Show Profile  Visit robvolk's Homepage  Reply with Quote
Unfortunately the 255 character maximum won't make this practical to run on 6.5. The INFORMATION_SCHEMA views don't exist in 6.5 either. The SubString() function can be used instead of the LEFT function.

We have been working on the ability to EXEC a text column, which would solve this particular limitation for you. However, I haven't applied the techinique to the pivot table code yet. Feel free to take a stab at it yourself, here's something that should get you started:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10245&FORUM_ID=5&CAT_ID=3&Topic_Title=Text+data+question&Forum_Title=Developer

Ilya has some code here that would allow you to EXEC large strings of SQL commands, but you'd have to craft the code to generate the pivot values yourself.

As for placing values in the cross-tab, you can use Min or Max, but you have to use some kind of summary function because the cross-tab is generated using GROUP BY. There's no way around this. If you only have one value for the row-column intersection anyway then Min and Max are fine.

Another option, if you have a fixed number of pivot columns, is to write CASE statements to generate each pivot column:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1


This was in Books Online, but not for 6.5. There should be a similar example if you search for "cross-tab". You can substitute Max for Sum.

Go to Top of Page

sommai
Starting Member

3 Posts

Posted - 12/02/2001 :  21:40:42  Show Profile  Reply with Quote
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??


Go to Top of Page

tony.holm
Starting Member

USA
2 Posts

Posted - 01/18/2002 :  13:24:30  Show Profile  Reply with Quote
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.........

Go to Top of Page

tony.holm
Starting Member

USA
2 Posts

Posted - 01/23/2002 :  12:52:32  Show Profile  Reply with Quote
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.



Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 01/23/2002 :  13:05:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

hoelo
Starting Member

USA
1 Posts

Posted - 01/29/2002 :  17:52:26  Show Profile  Send hoelo a Yahoo! Message  Reply with Quote
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)
Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 01/29/2002 :  18:39:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

jcoahran
Starting Member

1 Posts

Posted - 02/08/2002 :  15:38:01  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 02/08/2002 :  15:50:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yeah, go to page 2 of these replies, GreatInca and cwburke have some code that don't use global temp tables.

Go to Top of Page

WebPartz
Starting Member

1 Posts

Posted - 04/08/2002 :  13:38:00  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 04/08/2002 :  14:00:33  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

abdenn75
Starting Member

1 Posts

Posted - 05/20/2002 :  13:10:12  Show Profile  Reply with Quote
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


Go to Top of Page

mikeosmith
Starting Member

1 Posts

Posted - 07/09/2002 :  16:02:52  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 12 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.28 seconds. Powered By: Snitz Forums 2000