Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-08-31 : 10:43:32
|
| Adam writes "I have recently found your page: ItemID=2955 which defines a stored procedure.This sounds ideal for my problem, but I can't seem to implement it. I am not too familiar with stored procedures, but have copied and pasted your code into the SQL box within my database, but it has returned the error message: "#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE crosstab @select varchar(8000),@sumfunc varchar(1 "I basically have a small database with 3 important fields.A date (only date, no time), a string (roughly 10 characters and an amount field (currency). I require these to be displayed in a crosstable manner, with either the date or string along the top and side, and the currency to fill the table.I would be most grateful if you could either provide some advise, or even some sample code.Yours gratefully,Adam" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-08-31 : 10:49:21
|
| MySQL does not support stored procedures except in version 5, and its syntax differs from MS-SQL Server (SQLTeam is a SQL Server site, we don't do MySQL).The essence of the stored procedure is to generate a SQL statement containing all the CASE expressions needed to perform the cross tab, something like:SELECT Name, Sum(CASE WHEN Region='North' THEN Amount END) AS North,Sum(CASE WHEN Region='South' THEN Amount END) AS South,Sum(CASE WHEN Region='East' THEN Amount END) AS East,Sum(CASE WHEN Region='West' THEN Amount END) AS West,Sum(Amount) AS TotalFROM Sales GROUP BY NameThat statement syntax should work in MySQL (not 100% sure, but it's pretty standard). Getting MySQL to generate it dynamically is beyond my knowledge. Take a look in the MySQL forum over at http://dbforums.com/. There might be other ways to do dynamic crosstabs. |
 |
|
|
|
|
|
|
|
MYSQL Dynamic Cross-Tabs/Pivot Tables