froscon06 1.02

FrOSCon 2006
Free and Open Source Software Conference

Speakers
Giuseppe Maxia
Schedule
Tag 1
Location HS5
Zeit 15:00
Dauer 01:00
INFO
ID 44
Veranstaltungsart Workshop
Track DBMS
Sprache englisch

Pivot tables in MySQL 5

creating cross tabulations with MySQL 5 stored routines

Pivot tables (or crosstabs) are statistical reports where results are grouped by rows and the values are broken down by the values of one or more column. Server-side calculation of crosstab are achieved via complex SQL calls that can be created using Mysql 5 stored procedures and dynamic queries.

Now that MySQL 5 has reached stability, it's high time to consider those advanced functions that meet enterprise needs. One of the most requested features among the users base is crosstabs, also known as pivot tables. Crosstabs are statistical reports where results are grouped by rows and the values are broken down by the values of one or more column. Several client-side applications can calculate crosstabs (spreadsheets are a popular choice), but sometimes passing the data from the server to the client for calculation is not a vieble solution, due to the amount of data involved. Server-side crosstabs can be calculated in pure SQL, and this has been known for long time (http://dev.mysql.com/tech-resources/articles/wizard/index.html, http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html). However, creating such queries required a supporting language (C, C++, Prl, Java, etc), thus limiting the applicability of said solution to the users of that specific language. With MySQL 5, however, thanks to stored procedures and dynamic queries, developers can create crosstabs server-side crosstab queries no matter which host language they are using. There are several techniques that can be used to produce crosstab queries. This workshop will discuss the alternatives, weighting the pro and con of each, and giving plenty of examples, in hope that this material could contribute to the establishment of native Business Intelligence functions in the next versions of MySQL.