| Author |
 Topic  |
|
Vorbis
Starting Member
South Africa
6 Posts |
 Posted - 10/10/2001 : 08:57:48
|
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 |
 |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 10/10/2001 : 15:04:41
|
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!
|
|
|
|
GreatInca
Posting Yak Master
USA
102 Posts |
Posted - 10/10/2001 : 17:50:29
|
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)
|
|
|
|
Vorbis
Starting Member
South Africa
6 Posts |
Posted - 10/11/2001 : 03:05:01
|
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!
|
|
|
|
Vorbis
Starting Member
South Africa
6 Posts |
Posted - 10/11/2001 : 04:20:09
|
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
|
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 10/11/2001 : 04:36:37
|
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 |
|
|
|
sommai
Starting Member
3 Posts |
Posted - 11/02/2001 : 03:16:12
|
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??
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 11/02/2001 : 08:41:35
|
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.
|
|
|
|
sommai
Starting Member
3 Posts |
Posted - 12/02/2001 : 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
USA
2 Posts |
Posted - 01/18/2002 : 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
USA
2 Posts |
Posted - 01/23/2002 : 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
USA
15559 Posts |
Posted - 01/23/2002 : 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
USA
1 Posts |
Posted - 01/29/2002 : 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
USA
15559 Posts |
Posted - 01/29/2002 : 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 Posts |
Posted - 02/08/2002 : 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
USA
15559 Posts |
Posted - 02/08/2002 : 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 Posts |
Posted - 04/08/2002 : 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
USA
15559 Posts |
Posted - 04/08/2002 : 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 Posts |
Posted - 05/20/2002 : 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 Posts |
Posted - 07/09/2002 : 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 |
|
|
|
Topic |
|
|
|
Article: Dynamic Cross-Tabs/Pivot Tables
