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

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 07/12/2002 :  12:24:29  Show Profile  Visit Corobori's Homepage  Reply with Quote
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


Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 07/12/2002 :  12:34:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

Johann Von Satan
Starting Member

USA
1 Posts

Posted - 08/09/2002 :  19:56:24  Show Profile  Visit Johann Von Satan's Homepage  Send Johann Von Satan an AOL message  Reply with Quote
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


Go to Top of Page

steve_borkowski
Starting Member

USA
2 Posts

Posted - 01/15/2003 :  16:23:21  Show Profile  Visit steve_borkowski's Homepage  Reply with Quote
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
Go to Top of Page

steve_borkowski
Starting Member

USA
2 Posts

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

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/04/2003 :  16:19:47  Show Profile  Reply with Quote
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!!!

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 03/04/2003 :  17:00:28  Show Profile  Reply with Quote
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'

Go to Top of Page

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/04/2003 :  17:46:15  Show Profile  Reply with Quote
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
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 03/04/2003 :  19:10:37  Show Profile  Reply with Quote
I used the one in the article.


Go to Top of Page

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/04/2003 :  21:17:10  Show Profile  Reply with Quote
I tried the one in the article as well, but all i got was
Incorrect Syntax Near Keyword 'END'
even in QA...?!


Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 03/04/2003 :  21:37:09  Show Profile  Reply with Quote
Here copy this and try it. I'm using SQL SERVER 2k SP3


DROP PROCEDURE crosstab
GO
CREATE PROCEDURE 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

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')

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

GO

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'








Edited by - ValterBorges on 03/04/2003 21:38:08
Go to Top of Page

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/05/2003 :  03:06:42  Show Profile  Reply with Quote
Thanks a lot!! really appreciate it. it works on my local machine, now the problem is when I copy all these to my commercial database
hosting sql server, it doesn't work, it throws that
"Incorrect Syntax Near Keyword 'END'" exception.
so does that mean they haven't got the service pack installed?
or some other reasons?
and eventually I need to call these proc from my asp.net page and
bind the results into a datagrid, now would that be a problem for
using this crosstab procedure?
thanks again.


Go to Top of Page

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/05/2003 :  03:57:53  Show Profile  Reply with Quote
I got a response from them, they wanted me to check the version of sql server i have on my machine, which is

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2002 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: )

and their version is

Microsoft SQL Server 2000 - 8.00.665 (Intel X86) Jul 29 2002 15:02:32
Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on
Windows NT 5.0 (Build 2195: Service Pack 3)

seems like they got SP3 as well, hm..... then why that proc wouldn't work??

Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/05/2003 :  07:29:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
If you are pivoting a lot of columns, or, the values have very long names, you will exceed the limit on the varchar variables used in the procedure. Even using 2 variables won't protect you from this unless you're diligent in testing for it. Looking at the code you're using, it's possible that it's only grabbing part of the CASE expression. This is usually the case when you get the "syntax error near END" message. The fact that it works on one machine and not the other suggests this, the server it doesn't work on probably has more values to pivot than the other machine.

Go to Top of Page

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/05/2003 :  23:08:27  Show Profile  Reply with Quote
Thanks for all your help, I got another reply from them suggesting me to get SP3 installed and re-test it. but is there a general sulotion
to this? is there another crosstab SP i can use? I have found only a couple of them, this one is more suitable for me, because of the fact that I could combine data from different tables. hm... I guess if not then I have to do all these pivoting and things programmatically in asp.net after getting the results from the db in sql server.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/05/2003 :  23:24:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
There's a component/program called RAC, if you search SQL Team for it, or "cross tab" you'll find a link for it.

Ultimately what this procedure does is generate the CASE expressions that perform the cross-tab feature, and does it dynamically, so you don't have to manually build the statement. Realizing that this is the goal, you can tweak and modify the code to ensure that the syntax of the statement doesn't get broken for long SQL statements. Just to get a feel for what it outputs, change the EXEC(@sql) line to PRINT @sql or SELECT @sql on the machine that executes it correctly (or use one of the article examples) and look at how the output is structured.

Also look in these comments a little ways back, there's a version of the code that I posted that allows a WHERE clause for the pivot columns. That will let you limit the number of pivot columns you have and may solve the problem. It's usally a bad sign when the SQL gets so long as to be truncated, that usually only happens when you have more than 75-80 pivoting columns, which is far more than is practical for any kind of report.

Go to Top of Page

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/07/2003 :  19:58:44  Show Profile  Reply with Quote
Thanks for all your guy's help, found out what the problem realy was, it's got to do with access rights, funny thing is dat if I ticked the "System Administrators" tag under Server Roles, in the login properties under Security in the enterprise manager, then it works fine... :(... took me quite a while to solve this one..but then now the problem is I don't think my webhosting company would be willing to tick System Administrators tag for my login though, waiting for their reply anyways.


Go to Top of Page

edwardch
Starting Member

New Zealand
8 Posts

Posted - 03/09/2003 :  02:22:15  Show Profile  Reply with Quote
nope, they won't grant system administrator access to my account, need to think of another way... man why is it so hard to get this SP going!! does anyone know exactly which access right needs to be granted for executing this crosstab SP? i've tried almost everywhere.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/09/2003 :  09:18:59  Show Profile  Visit robvolk's Homepage  Reply with Quote
You need to have CREATE TABLE rights in tempdb, and SELECT permissions on any table(s) that would be included in the cross tab. The tempdb part shouldn't be a problem, you can test it easily with CREATE TABLE ##temp and DROP TABLE ##temp. If either of those throw errors, you'd have to get that fixed. You might also need to have them set the "select into/bulk copy" option to "on" for tempdb, they might've forgotten to set it (or came up with a really dumb reason to turn it off)

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/28/2003 :  17:01:40  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
In honor of Rob's great stored proc, here's an alternate one:

It will allow your cross-tab to summarize multiple values, it only creates columns based on your actual select statement ( not based on all rows in a table), and it allows you to easily specify default values for your pivots.

Also, it's shorter and hopefully easier to follow and modify as needed.


create procedure CrossTab2 (@SQL varchar(1000),	
	@PivotCol varchar(100),
	@Summaries varchar(100), 
	@GroupBy varchar(100),
	@OtherFields varchar(100) = Null)
AS

set nocount on

declare @Vals varchar(5000);
set @Vals = '';

set @OtherFields = isNull(', ' + @OtherFields ,'')

exec ('SELECT Distinct convert(varchar(100),' + @PivotCol + ') as Pivot INTO ##Temp FROM (' +
	 @SQL + ') A')

select @Vals = @Vals + ', ' + 
    replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + Pivot + 
	''' THEN '),')[', ' END) as [' + Pivot )
from ##Temp order by Pivot

drop table ##Temp

set nocount off

exec ( 'select ' + @GroupBy + @OtherFields + @Vals + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)


---------------
There are some big differences from Rob's, so let's look at the parameters:

SQL
This is the SQL statement or table you are querying. It can be any valid SELECT statement.

PivotCol
This is the column or an expression you wish to pivot by

Summaries
The summaries you wish to perform. Note that this is plural; you can summarize multiple columns !
Here is the format of this field:

SUM(Field ELSE DefaultValue)[FieldName], ....

First, list the aggregate function you wish to use, and within that function put the field you wish to summarize, and add an ELSE clause with what that column's default value is. For example, put 0 if you wish to display all 0's for empty columns, or NULL if you wish to display nulls.

Next, immediately following the aggregate function (with no spaces) put the field name in brackets. This is what will be appended to the beginning of the field name, followed by the value from the pivot column. Leave as [] to just use the pivot column's value as the field name.

Finally, you may have multiple summaries just seperate them by comma's. Remember if you have many pivot values, you will probably end up with too many columns and/or a T-SQL statement > 8000 characters so you may get an error.

Examples:

If the pivot column is "Employee", with values of 001, 002, 003 and 004:

SUM(Hours ELSE 0)[]

returns column headings of 001, 002, 003, 004, and returns a 0 in all "empty" fields.

SUM(Hours ELSE 0)[Hours], MAX(Date ELSE Null)[MaxDate]

returns column headings of Hours001, MaxDate001, Hours002, MaxDate002, Hours003, MaxDate003, ..etc ...

SUM(Amount ELSE Null)[]

returns column headings of 001,002,003,004 and returns a NULL in all "empty" fields.

SUM(Amount ELSE 0)[Amount], COUNT(Amount)[Qty]

returns column headings of Amount001, Qty001, Amount002, Qty002, ... etc ...

GroupBy
This is the list of the non-pivot columns you wish to group by and return, separated by commas.

OtherFields (optional)
Any other fields to return that you are not grouping by; make sure it is valid and doesn't contain non-aggregate fields that do not appear in the group by clause.

-----------------------
Some samples, all derived from Rob's samples in his article.

From Northwind:

exec CrossTab2
'SELECT LastName, OrderDate FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'Year(OrderDate)',
'Count(LastName)[]',
'LastName'

exec CrossTab2
'select titles.*, stores.stor_name, sales.qty, sales.stor_id from pubs..titles titles inner join pubs..sales sales on (sales.title_id=titles.title_id) inner join pubs..stores stores on sales.stor_id = stores.stor_id ',
'stor_id',
'SUM(qty ELSE 0)[Qty], MAX(stor_name ELSE '''')[MaxStoreName], COUNT(1 ELSE 0)[Count]',
'title',
'Count(*) as TotalCount'


- Jeff

Edited by - jsmith8858 on 03/02/2005 12:43:57
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 0.69 seconds. Powered By: Snitz Forums 2000