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
Next Page
Author Previous Topic Topic Next Topic
Page: of 12

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/02/2001 :  20:41:33  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Allan
Starting Member

Australia
1 Posts

Posted - 06/07/2001 :  01:23:06  Show Profile  Visit Allan's Homepage  Reply with 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?

Go to Top of Page

RobWafle
Starting Member

38 Posts

Posted - 06/19/2001 :  11:00:51  Show Profile  Visit RobWafle's Homepage  Send RobWafle an AOL message  Send RobWafle an ICQ Message  Send RobWafle a Yahoo! Message  Reply with Quote
Ok, I love this.. and I used this and it worked awesome.. but now I have reached the limits of this code..

My problem seems to be that I have too many unique items in my pivot column. I only want to pivot on colums that match a certain criteria..

anyone have any ideas? e.g. i have 200 years of data, but I want to pivot on the last three.. or 1980, 1981, and 1982..

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 06/19/2001 :  14:02:58  Show Profile  Visit robvolk's Homepage  Reply with Quote
I have a quick patch, use the following code:

CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@where varchar(1000)=null
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 ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ IsNull(@where,'1=1') + ' AND ' + @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='##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 ##pivot

DROP TABLE ##pivot

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

EXEC (@select)
SET ANSI_WARNINGS ON


I added an optional @where parameter to the end. Put in a valid WHERE clause, like 'year(dateColumn) Between 1980 and 1982', in this @where parameter. I will probably add this to the article, and hopefully work around the global temp table problem too :)

Thanks for the suggestion on how to improve it! I wasn't sure anyone read that part :)

Edited by - robvolk on 06/19/2001 14:03:26
Go to Top of Page

olgcic
Starting Member

1 Posts

Posted - 06/29/2001 :  19:01:55  Show Profile  Reply with Quote
Just what I needed - great job. Any idea when you'll have the global temp table issue figured out? Thanks for your help!!

Go to Top of Page

tvogel
Starting Member

USA
3 Posts

Posted - 07/21/2001 :  17:08:48  Show Profile  Reply with Quote
I changed your revised script a little to get it to compile in MS SQL Server 2000.

changed
@where varchar(1000)=null to @where varchar(1000)='1=1'

and
+ IsNull(@where,'1=1') to + @where

This accomplishes the same thing and will compile correctly

Go to Top of Page

tvogel
Starting Member

USA
3 Posts

Posted - 07/21/2001 :  18:38:44  Show Profile  Reply with Quote
I am trying to use your revised cross tab proc in a SHAPE command. I receive the runtime error '80040e14', Provider command for child rowset does not produce a rowset.

Any ideas on how to correct this error?

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 07/21/2001 :  18:59:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
Thanks for fixing the code for SQL 2000 and posting it! I don't have SQL2K and couldn't test that out.

I'm not too sure that SHAPE works with stored procedures in SQL Server, at least not as child recordsets. I've never tried it, but I remember someone posting a similar problem and it was determined that it didn't work. (if you've gotten it to work, cool!)

I don't think it will work with this one anyway since it is dynamic SQL, and SHAPE won't have a fixed data structure to work with.

Can you provide some code and a description of what you want to do? I might be able to think of a way around it.

Go to Top of Page

tvogel
Starting Member

USA
3 Posts

Posted - 07/21/2001 :  19:46:17  Show Profile  Reply with Quote
Saturday evening and a response in less than an hour ?!?!?!? Do we have real lives :}

In SQL2000 stored procs work fine in parent & child (n-deep) SHAPE commands.

Background
Application is a grade book for teachers. A teacher has multiple classes, students can belong to zero or more classes, assignments are given to students in a class on a given date but not all students may have been in class on that date

3 entity and 3 relationship tables (other fields left off for brevity)
Class
ClassID
Descr

Student
StudentID
Name

Assignment
AssignID
Descr
PointsPossible

ClassAssign
ClassID
AssignID
AssignDate
DueDate

StudentClass
StudentID
ClassID
AssignedSeat

StudentAssign
StudentID
AssignID
PointsEarned


Hierarchical recordset output desired
Class.descr, ....
--Student(1).name AssignID(1).PointsEarned AssignID(2).PointsEarned ...
--Student(2).name AssignID(1).PointsEarned AssignID(2).PointsEarned ...

This really collapses the assignment grandchild level into a crosstab on the child level. The result is something that looks a lot like a basic paper grid that teachers are used to completing.

Here is the SHAPE command that I am trying:
SHAPE {{ CALL dbo.sp_GetClassStudent (1) }} AS ClassSimple
APPEND ({{ CALL dbo.sp_CrossTab ('SELECT studentid FROM studentassign GROUP BY studentid' ,
'sum(PointsEarned)', 'AssignID', 'classassign', 'classid=1') }} AS StudentSimple
RELATE 'StudentID' TO 'StudentID') AS StudentSimple

sp_GetClassStudent - returns all of the students in a particular class

Thanks in advance
Tim


Go to Top of Page

djohnson
Starting Member

2 Posts

Posted - 08/06/2001 :  12:35:29  Show Profile  Send djohnson an AOL message  Reply with Quote
I've tried using #pivot to create a local temp table instead of using a global temp table, but it doesn't work. Has anyone been able to resolve this?

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?





Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/23/2001 :  16:14:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
What exactly do you mean by fractionary numbers?

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 08/24/2001 :  09:42:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
What exactly do you want to do with them? They're numbers like any other, all of the aggregate functions will work on them (Min, Max, Avg, Sum, etc.)

If you're trying to pivot real values and create column headings out of them, you may need to use the CONVERT() or STR() functions to convert them into character data. STR() is more flexible, it lets you set the decimal precision and total length of the number.

Go to Top of Page

Alerson
Starting Member

Brazil
5 Posts

Posted - 08/31/2001 :  13:11:48  Show Profile  Reply with Quote
How can I grant execution permissions on crosstab stored procedures to a Domain Users?
Go to Top of Page

phillipsriii
Starting Member

1 Posts

Posted - 09/28/2001 :  17:21:20  Show Profile  Send phillipsriii a Yahoo! Message  Reply with Quote
1. Can we use add timestamp or something like that to the ##pivot string so that multiple users will not access the same global table? if so, will there be any peformance problems.

Ex: change all '##pivot' TO '##pivot_' + stamp + '' and
change DROP TABLE ##pivot_stamp TO exec('drop table ##pivot_' + stamp + ' ')

currently i'm unable to test this code because the "tempdb.information_schema.columns" does not exist in my SQL 7.0 database. is this a SQL 2000 object?



Edited by - phillipsriii on 09/28/2001 17:22:47
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 09/28/2001 :  18:44:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
tempdb.information_schema.columns DOES exist in SQL 7.0 and above, I tested the code sucessfully on SQL 7.0. There is a server setting that hides system objects, maybe that setting was enabled when you registered the server. Try re-registering the server in Enterprise manager and make sure the "Show System Databases" option is checked.

I have recently been working on a method that would allow normal temp tables to be used, but I have not yet finished it. Check back in the next week for an update on the article code, I should have it finished by then.

Go to Top of Page

cwburke
Starting Member

16 Posts

Posted - 10/09/2001 :  07:55:47  Show Profile  Reply with 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
15676 Posts

Posted - 10/09/2001 :  14:12:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
How are you calling it in VB? Usually when I get an error like that it's because of a single quote or double quote that got added or orphaned. Post your VB code if you can.

Go to Top of Page

cwburke
Starting Member

16 Posts

Posted - 10/09/2001 :  14:59:02  Show Profile  Reply with Quote
It is actually called by another stored procedure, which I call. Here is what I do in VB:

sqlstr = "rep_SKUxMOL"
rs.Open sqlstr, oConn, adOpenStatic, adLockReadOnly

rep_SKUxMOL is a stored procedure that creates the sql statement that is passed into your crosstab stored procedure. I can run rep_SKUxMOL from QA just fine, but get the error when I try and run it from VB.

I know I should incorporate the rep_SKUxMOL into my app, but I'm just experimenting right now just trying to get it to work. It may be a mute point anyhow because the @Select string the crosstab procedure creates is > 8000 characters. Don't guess there is anyway around that?!!

Thanks,

Chris


Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 10/09/2001 :  15:04:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
Well, THAT'S what's causing the problem...hmmmmm, you DIDN'T mention that before

There is a way around it, you need to split the SQL statement up into 8000 byte chunks. Doing this dynamically exceeds my talents (and patience), so if you figure it out, I owe you a CASE of beer. If you can get the statement into separate chunks (@sql1, @sql2, etc.) then this will work:

EXEC (@sql1 + @sql2 + @sql3)

Go to Top of Page

cwburke
Starting Member

16 Posts

Posted - 10/09/2001 :  15:20:36  Show Profile  Reply with 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

Go to Top of Page

cwburke
Starting Member

16 Posts

Posted - 10/10/2001 :  08:20:13  Show Profile  Reply with Quote
I was able to split the statement into two seperate chunks. I had to add an order by statement to the "INSERT INTO ##pivot" declaration to make sure that my pivot columns were ordered. This may need a little tweaking, but it works for me!!

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


Chris


Go to Top of Page
Page: of 12 Previous Topic Topic Next Topic  
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 0.83 seconds. Powered By: Snitz Forums 2000