Folks, I've searched for help in seemingly more relevant places, but I have a suspicion someone here will at least point me. I've googled, search the manual, read heaps of tutorials (devshed etc) but I don't know what to *call* what I'm after, so it's very tricky. Could someone direct me to a tutorial or example that deals with rearranging shown below MySQL... Or at least give me the *jargon* for what I'm trying to do. I don't think it's a cross tab or pivot table, as they seem to sum data. I have this table: name week score joe 1 30 fred 1 23 rita 1 33 joe 2 31 fred 2 24 rita 2 31 And I just want to display it like this... name week1 week2 week3 joe 30 31 fred 23 24 rita 33 31 What do I call that please? Ta in advance -- Matt Johnson __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
Yup it's a cross tab alright. Don't need to sum can use first or max or whatever. SQL looks like TRANSFORM First(table.Score) AS score SELECT table.name FROM table GROUP BY table.name PIVOT table.week; this gives the columns with names like 1,2,3 which doesn't really matter as you can pretty it up in a suitable reporting package. John McCabe CIS Manager East Norfolk College
-----Original Message----- From: Matt Johnson [mailto:johnsonmlw@yahoo.com] Sent: 07 March 2003 11:24 am To: SuSe Subject: [suse-linux-uk-schools] MySQL
Folks,
I've searched for help in seemingly more relevant places, but I have a suspicion someone here will at least point me. I've googled, search the manual, read heaps of tutorials (devshed etc) but I don't know what to *call* what I'm after, so it's very tricky.
Could someone direct me to a tutorial or example that deals with rearranging shown below MySQL... Or at least give me the *jargon* for what I'm trying to do.
I don't think it's a cross tab or pivot table, as they seem to sum data.
I have this table:
name week score joe 1 30 fred 1 23 rita 1 33 joe 2 31 fred 2 24 rita 2 31
And I just want to display it like this...
name week1 week2 week3 joe 30 31 fred 23 24 rita 33 31
What do I call that please?
Ta in advance
-- Matt Johnson
__________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
-- To unsubscribe, e-mail: suse-linux-uk-schools-unsubscribe@suse.com For additional commands, e-mail: suse-linux-uk-schools-help@suse.com
--- John McCabe
Yup it's a cross tab alright. Don't need to sum can use first or max or whatever. SQL looks like
TRANSFORM First(table.Score) AS score SELECT table.name FROM table GROUP BY table.name PIVOT table.week;
Thanks for you help John. Unfortunately, MySQL doesn't understand 'TRANSFORM' or 'PIVOT'. I'm still searching through google. There seems little on it, and I would imagine it's a hugely required query. Very odd. Thanks anyway... I'll find the MySQL mailing list and subscribe to that. If anyone has any suggestions, perhaps mail me off list? Much appreciated. Ta -- Matt Johnson __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
participants (2)
-
John McCabe
-
Matt Johnson