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

FlyBoy
Starting Member

United Kingdom
1 Posts

Posted - 05/09/2003 :  21:05:53  Show Profile  Reply with Quote
Hey Edwardch

I had the same "syntax error near END" problem as you. Worked it out by logging in as SQLAdmin and then as a poor user. In order to select from tempdb the user has to have db_reader rights on temp db. Try getting your webhosting guys to let you have this read only access. Worth a go .....

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 05/10/2003 :  10:49:00  Show Profile  Reply with Quote
Jeff,

Nice mod,
The need to summarize multiple alternating columns like hours,amount occurs frequently.

I'm thinking this portion could be replace by an openquery call.

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

Something like

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


then there would be no need for a global temp table.

Will play around with it, it looks promising.






Edited by - ValterBorges on 05/10/2003 10:52:00
Go to Top of Page

clemmons42
Starting Member

5 Posts

Posted - 05/16/2003 :  11:12:55  Show Profile  Reply with Quote
I am running Jeff's stored procedure from Visual Basic 6.0 and I am getting the following error:

Line 1: Incorrect syntax near ','.

I can run this stored procedure from the Query Analyzer just fine but not from VB. Below is the routine that I am using to call the stored procedure:

any help would be appreciated.

Thanks,
Mike

-------------------------------------
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim params As ADODB.Parameters
Dim param As ADODB.Parameter

' Create connection and command objects
Set cmd = New ADODB.Command

' Set command properties
With cmd
Set .ActiveConnection = gDB.DB
.CommandText = "CrossTab2"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

' Define stored procedure params and append to command.
params.Append cmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
params.Append cmd.CreateParameter("@SQL", adVarChar, adParamInput, 1000)
params.Append cmd.CreateParameter("@PivotCol", adVarChar, adParamInput, 100)
params.Append cmd.CreateParameter("@Summaries", adVarChar, adParamInput, 100)
params.Append cmd.CreateParameter("@GroupBy", adVarChar, adParamInput, 100)
params.Append cmd.CreateParameter("@OtherFields", adVarChar, adParamInput, 100)

' Specify input parameter values
params("@SQL") = "SELECT Name, Yld_Adj, Loc_Code, AdjYldMean FROM tblSoybeans where Trial='" & sTest & "'"
params("@PivotCol") = "Loc_Code"
params("@Summaries") = "AVG(Yld_Adj)[], avg(AdjYldMean)[]"
params("@GroupBy") = "Name"
params("@OtherFields") = ""

' Execute the command
Set rs = cmd.Execute
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/16/2003 :  11:39:54  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
If you want to aggregate more than 1 value, you need to assign at least 1 of them alternate field names. i think that may be your problem.

Try:

params("@Summaries") = "AVG(Yld_Adj)[], avg(AdjYldMean)[Adj]"

or something like that. Let me know if that works.

- Jeff
Go to Top of Page

clemmons42
Starting Member

5 Posts

Posted - 05/16/2003 :  11:58:58  Show Profile  Reply with Quote
quote:

If you want to aggregate more than 1 value, you need to assign at least 1 of them alternate field names. i think that may be your problem.

Try:

params("@Summaries") = "AVG(Yld_Adj)[], avg(AdjYldMean)[Adj]"

or something like that. Let me know if that works.

- Jeff


Jeff,
I am getting the same error. I also tried putting a column name in for the first one as well.

Thank you for helping.

Mike

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/16/2003 :  12:14:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Print out the SQL instead of exec'ing and take look ... is it going over the 8000 character limit?

- Jeff
Go to Top of Page

clemmons42
Starting Member

5 Posts

Posted - 05/16/2003 :  12:25:08  Show Profile  Reply with Quote
quote:

Print out the SQL instead of exec'ing and take look ... is it going over the 8000 character limit?

- Jeff



here is the output i get when i run it via the Query Analyzer:

AG3702 .000000 NULL .000000 NULL .000000 NULL
AG4602 .000000 NULL .000000 NULL .000000 NULL
CSR4112 .000000 NULL .000000 NULL .000000 NULL
M13502 .000000 NULL .000000 NULL .000000 NULL
M13503 .000000 NULL .000000 NULL .000000 NULL
M13504 .000000 NULL .000000 NULL .000000 NULL
M13505 .000000 NULL .000000 NULL .000000 NULL
M13506 .000000 NULL .000000 NULL .000000 NULL
M13507 .000000 NULL .000000 NULL .000000 NULL
M13508 .000000 NULL .000000 NULL .000000 NULL
M13509 .000000 NULL .000000 NULL .000000 NULL
M13510 .000000 NULL .000000 NULL .000000 NULL
M13511 .000000 NULL .000000 NULL .000000 NULL
M13512 .000000 NULL .000000 NULL .000000 NULL


This is the line that i use to execute it in the Query Analyzer:
exec crosstab2
'SELECT Name, Yld_Adj, Loc_Code, AdjYldMean FROM tblSoybeans where Trial="4EBT"',
'Loc_Code',
'AVG(Yld_Adj)[AdjYield], avg(AdjYldMean)[YldMean]',
'Name'




Go to Top of Page

clemmons42
Starting Member

5 Posts

Posted - 05/16/2003 :  13:22:03  Show Profile  Reply with Quote
I am able to run Robs code from visual basic but I haven't yet got it working with your code. I really need the flexibility that your code offers. I still trying anything i can think of.

thanks for the help
Mike

Go to Top of Page

markofsoton
Starting Member

4 Posts

Posted - 06/20/2003 :  04:08:58  Show Profile  Reply with Quote
Not Totally sure if this is the right place...

Robs code is the answer to my prayers however i am getting a rather ambiguous error running a ct in query analyzer.

Maybe i am a bit dumb but

EXEC crosstab 'SELECT A.timeSortName, B.aperWeekNumber, C.taskDescription, A.timeHrsST
FROM dbo.tblTIMEMainData A INNER JOIN dbo.tblLOOKPeriod B ON A.timePeriodName = B.aperPeriodName INNER JOIN
dbo.tblLOOKProjTask C ON A.timeTaskID = C.taskID
GROUP BY A.timeSortName, C.taskDescription, A.timeHrsST, B.aperWeekNumber, C.taskCode
HAVING (B.aperWeekNumber = 5)', 'sum(A.timeHrsST)', 'taskDescription', 'dbo.tblLOOKProjTask'


throws up

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

bit of a rookie in sql server so any help appreciated

Go to Top of Page

markofsoton
Starting Member

4 Posts

Posted - 06/20/2003 :  04:54:58  Show Profile  Reply with Quote
don't worry about it it was only to do with whitespace soon as i concatanated the select statement onto one line it worked lovely.

cheers mark

Go to Top of Page

markofsoton
Starting Member

4 Posts

Posted - 08/19/2003 :  11:16:55  Show Profile  Reply with Quote
I have had this working for a while via an asp page, however something has been reset. and i am getting

Microsoft OLE DB Provider for SQL Server error '80040e09'

SELECT permission denied on object 'sysobjects', database 'tempdb', owner 'dbo'.

/timesheets/cttest.asp, line 422

I have tried adding all sorts of permisions including select rights to the public roles and even the iusr and iwam accounts but i keep getting this error, going a bit mad with it

i think something that has changed is that the dbo account has has had its datareader and writer permissions switched off. and i get a

cannot use the reserved user or role name 'dbo'

but i don't remember please any help

Thanks

Mark


Go to Top of Page

markofsoton
Starting Member

4 Posts

Posted - 08/19/2003 :  11:19:43  Show Profile  Reply with Quote
I should add it works peachy in query analyzer
Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 08/19/2003 :  14:20:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
I saw something similar happen on a SQL Server 2000 machine, I believe it may be related to the service packs. Which version and service pack are you using?

You should also double check that the ASP login has CREATE TABLE permission on tempdb (it probably already does, but check anyway) You might want to try a test using the other working login if it's different from the one used by the ASP page.
Go to Top of Page

mendina
Starting Member

2 Posts

Posted - 10/27/2003 :  12:32:10  Show Profile  Visit mendina's Homepage  Reply with Quote
This is an old thread, but I was glad when I ran across it. It looked like a good solution...and it worked fine in query analyzer.

But, it can't be used by my .net applications, even when they're accessing the database as sa.

In particular, the statement:
quote:

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 order by pivot



doesn't execute even once. When accessing the stored procedure using ado.net, I can return the contents of the ##pivot table, but the query above does nothing, and doesn't return an error message.

Has anyone gotten this to work from a vb or c# .net application?
Go to Top of Page

mendina
Starting Member

2 Posts

Posted - 10/27/2003 :  12:34:33  Show Profile  Visit mendina's Homepage  Reply with Quote
I should probably have mentioned that I'm accessing a sql server 2000 sp3 database, in case it matters. Whoops!
Go to Top of Page

clemmons42
Starting Member

5 Posts

Posted - 11/04/2003 :  08:56:30  Show Profile  Reply with Quote
mendina,
This may seem odd, but I was getting the error when trying to execute this SP from a vb app. What I had to do to get it to work, was to actually create another Stored Procedure that called the original stored procedure. Don't ask me why this works, but it does.

Mike Clemmons
Go to Top of Page

jlinvillewvuit
Starting Member

1 Posts

Posted - 12/09/2003 :  16:34:54  Show Profile  Reply with Quote
This SP works great but I have a question I'm hoping you guys can help me with. I'm a bit of a SQL rookie but I was able to get this SP working the way I needed it to.

My question concerns using the resultset that this SP returns. I'm wanting to call this stored procedure from another stored procedure or possibly from just a SQL statement in C#. I need to join the results of this stored procedure with some other tables I have.

How do you join the results of a stored procedure with another table in a select statment? Is that even possible.

I thought at first about creating a temporary table to hold the output of the stored procedure but the amount of columns is dynamic of course so I'm not sure how to do this.

I'm wanting to do something like this....

select q.*

from Projects as p

inner join (
EXEC Crosstab
'SELECT ProjectGroups.Company_Cd, ProjectGroups.Project_No From ProjectGroups Inner Join GroupValues ON ProjectGroups.Group_ID=GroupValues.Group_Cd AND ProjectGroups.Group_Value_Cd=GroupValues.Group_Value_Cd INNER JOIN Groups ON Groups.Group_Cd=ProjectGroups.Group_Id GROUP BY ProjectGroups.Company_Cd,ProjectGroups.Project_No',
'MAX(Group_Value_Name)',
'Group_Name',
'Groups'
) as q
ON p.Company_Cd=q.Company_Cd AND p.Project_No=q.Project_No
order by
p.Company_Cd
,p.Project_No
Go to Top of Page

fishberg5051
Starting Member

2 Posts

Posted - 12/11/2003 :  11:14:50  Show Profile  Visit fishberg5051's Homepage  Send fishberg5051 an AOL message  Reply with Quote
Hey Everyone...I'm using Volks Example and I'm having some difficulty executing it. I'm on SQL 2000 server...SP's updated...and I'm using it as is straight from the article wrote..no modifications. I'm struggling on just the basic use of this (yeah I'm dumb)...so my question is: (4)Table ...what is that supposed to be... 1 is the select, 2 is the summary calculation, 3 is the Pivot, and 4 is the table....is the table supposed to be what your inserting your data into? Or Selecting from? I'm not quite understanding this...I thought I did it correctly in analyzer and I got these errors:

(5504 row(s) affected)
(5504 row(s) affected)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.

when I run it, it tries to work, and if I look quick it makes something, but then those errors appear...here is my query I'm using also:

IF (object_id('tempdb..#temp') is not null) DROP TABLE #temp
SELECT d.PartNumber, left(d.PartNumber,4) as CustomerPrefix, d.mdsoriginalqty, d.price,
d.amount, d.lineschdat, d.promisedate,
m.AssemblyResourceCost,
((d.mdsoriginalqty * m.AssemblyResourceCost)/9.25) as MDSUnitLaborHrs,
d.mdsoriginalqty/d.mdsoriginalqty as NumOfKits
into #temp FROM MDSReport d INNER JOIN manningforcast m ON d.PartNumber = m.Item
select * from #temp

exec p431master.dbo.crosstab 'select tempdb.dbo.#temp.CustomerPrefix from tempdb.dbo.#temp group by tempdb.dbo.#temp.CustomerPrefix','sum(tempdb.dbo.#temp.NumOfKits)','tempdb.dbo.#temp.lineschdat','tempdb.dbo.#temp'

I got all my data I want in that one #temp table produced by the first query, and then trying to use the crosstab query to produce the report I want. In case you cared it should look like:

12/1/2003 | 12/2/2003 | 12/3/2003 ..etc etc etc
Customername 5 (this is kits) 6 37 ..etc etc etc

I'm new to Stored Procedures/functions in SQL...and I've been reading all these posts and not sure about the 8000 character limit...I should also mention on my dates above, that goes out for a whole year...so 365 dates...is that possible? I would appreciate any help...I'll check back here periodically or email me at fishberg5051@hotmail.com Thanks in Advance..and I'll even throw in some beer if someone could help me with this. Thanks a bunch.

-Fish
Go to Top of Page

fishberg5051
Starting Member

2 Posts

Posted - 12/11/2003 :  14:35:53  Show Profile  Visit fishberg5051's Homepage  Send fishberg5051 an AOL message  Reply with Quote
Ok, I think I'm understanding this more...not quite sure on the 'END' thing...but I don't think that's my problem...it's the > 8000 characters....I think I have about 100000 characters....well maybe not that much exactly, but I have a CRAPload of data...and I'm using the SP that uses @SQL1 + @SQL2 + @SQL3 which is mixed with Rob's example. It's getting me closer...I guess my new question is...how do I go about adding more variables to the SP? So I'll have the code...and at the end it'll be something like Execute(@SQL1 + @SQL2 + @SQL3 + @SQL4 + @SQL5 + @SQL6..etc etc.)...is that feasable? Strain on the server is ok..for this will only be ran once a day..any hints suggestions would be greatly appreciated. Here is the current code I'm using:

CREATE PROCEDURE crosstabHMT8000
@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)

PRINT(@BeginSelect + @sql + @sql2 + ' ' + @EndSelect)
SET NOCOUNT ON
SET ANSI_WARNINGS ON
GO

Go to Top of Page

berylsmall
Starting Member

USA
26 Posts

Posted - 02/04/2004 :  09:19:22  Show Profile  Reply with Quote
Okay, this code works great in Northwind, but I can't seem to get it to work with my database. Can anyone tell me what I am doing wrong?
My Execute statement looks like this:
EXECUTE crosstab 'SELECT tblStores.StoreNumber FROM tblStores LEFT OUTER JOIN tblAdjustments RIGHT OUTER JOIN tblStoreAdjustmentsJunction ON tblAdjustments.AdjustID = tblStoreAdjustmentsJunction.AdjustmentID ON tblStores.StoreNumber = tblStoreAdjustmentsJunction.StoreID GROUP BY tblStores.StoreNumber', 'SUM(tblStoreAdjustmentsJunction.Quantity * tblAdjustments.AdjustmentAmount)', 'AdjustmentDate', 'tblAdjustments'

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.14 seconds. Powered By: Snitz Forums 2000