| Author |
 Topic  |
|
FlyBoy
Starting Member
United Kingdom
1 Posts |
 Posted - 05/09/2003 : 21:05:53
|
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 .....
|
 |
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 05/10/2003 : 10:49:00
|
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 |
|
|
|
clemmons42
Starting Member
5 Posts |
Posted - 05/16/2003 : 11:12:55
|
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
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/16/2003 : 11:39:54
|
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 |
|
|
|
clemmons42
Starting Member
5 Posts |
Posted - 05/16/2003 : 11:58:58
|
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
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/16/2003 : 12:14:31
|
Print out the SQL instead of exec'ing and take look ... is it going over the 8000 character limit?
- Jeff |
|
|
|
clemmons42
Starting Member
5 Posts |
Posted - 05/16/2003 : 12:25:08
|
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'
|
|
|
|
clemmons42
Starting Member
5 Posts |
Posted - 05/16/2003 : 13:22:03
|
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
|
|
|
|
markofsoton
Starting Member
4 Posts |
Posted - 06/20/2003 : 04:08:58
|
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
|
|
|
|
markofsoton
Starting Member
4 Posts |
Posted - 06/20/2003 : 04:54:58
|
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
|
|
|
|
markofsoton
Starting Member
4 Posts |
Posted - 08/19/2003 : 11:16:55
|
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
|
|
|
|
markofsoton
Starting Member
4 Posts |
Posted - 08/19/2003 : 11:19:43
|
| I should add it works peachy in query analyzer |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 08/19/2003 : 14:20:06
|
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. |
|
|
|
mendina
Starting Member
2 Posts |
Posted - 10/27/2003 : 12:32:10
|
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? |
|
|
|
mendina
Starting Member
2 Posts |
Posted - 10/27/2003 : 12:34:33
|
| I should probably have mentioned that I'm accessing a sql server 2000 sp3 database, in case it matters. Whoops! |
|
|
|
clemmons42
Starting Member
5 Posts |
Posted - 11/04/2003 : 08:56:30
|
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 |
|
|
|
jlinvillewvuit
Starting Member
1 Posts |
Posted - 12/09/2003 : 16:34:54
|
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
|
|
|
|
fishberg5051
Starting Member
2 Posts |
Posted - 12/11/2003 : 11:14:50
|
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
|
|
|
|
fishberg5051
Starting Member
2 Posts |
Posted - 12/11/2003 : 14:35:53
|
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
|
|
|
|
berylsmall
Starting Member
USA
26 Posts |
Posted - 02/04/2004 : 09:19:22
|
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'
|
|
|
|
Topic |
|
|
|
Article: Dynamic Cross-Tabs/Pivot Tables
