Robert Gravelle

When generating a result set where the query contains an unknown number of column and/or row values we can use a combination of Prepared Statements, which allows us to tailor the output based on the number of data values. We can also add input parameters to a procedure to assign the field names, aliases, and […]

MySQL Reporting requirements sometimes require both unknown column and row values, necessitating a more powerful means of generating crosstabs. Today’s article presents Prepared Statements, which dynamically generate the SQL and assign it to a variable so that we can tailor the output based on the number of data values. During the past several weeks, we’ve […]

Learn a technique to fetch column data for MySQL crosstabs by emulating views. By replacing tables with a subquery subset, the resulting view will contain a limited dataset that has been filtered for that column field. In the database realm, a view is a virtual or logical table, which contains the results of a SELECT […]

Inserting additional columns to a crosstab query is less straightforward than adding row data, because SQL naturally groups data by rows. Moreover, the efficacy of the CASE statement within an aggregate function decreases in direct proportion to the complexity of the criteria. Read on to learn more… Today’s topic is crosstabs, which contain multiple aggregate […]

MySQL’s crosstabs contain aggregate functions on two or more fields, presented in a tabular format. In a multi-aggregate crosstab query, two different functions can be applied to the same field or the same function can be applied to multiple fields on the same (row or column) axis. Rob Gravelle shows you how to apply two […]

Rob Gravelle takes a complex SQL statement and shows you several ways to re-write it that both simplifies and shortens it, while still yielding the same results. After the All About the Crosstab Query article was published, some astute readers pointed out that my SQL statement was more complex than it needed to be. Their […]

My last DatabaseJournal article,  All About the Crosstab Query, described how to formulate an SQL statement for generating a cross tabulation query. My original intention for today’s follow up was to explore the use of stored procedures to make crosstab generation more dynamic.  That was until a bug sent me on a search for answers.  […]

A cross tabulation query, or cross tab for short, is used to display the calculation of a sum, average, count, or other type of total on two or more fields. The results are then presented in a table format in which one field is displayed down the left side of a matrix (row headings) and […]

Did you know that MySQL has supported subqueries since version 4.1? In fact, it even boasts a few MySQL-specific features! Whether you’re well acquainted with subqueries or just a novice in this area, you’re bound to benefit from today’s article, which will cover how to use subqueries in MySQL to get the most out of […]

During the past few weeks, we’ve been getting better acquainted with XML support in MySQL. In the last article, we expanded the discussion to include a third-party tool called MySqlDump. We used it to produce an XML document that we queried using a stored procedure. Today, we’ll be finishing off the series by talking about […]

In the “Working with XML Data in MySQL” article, we explored some of MySQL’s XML capabilities, including the –xml command line option, which provides a means of formatting query output as XML, as well as a couple of new XML-specific functions that were introduced in MySQL 5.1.5. In today’s article, we’ll continue to explore XML […]

XML was created to facilitate the transportation of data between systems and businesses in a language nonspecific way. When first introduced, a script or application process had to parse the XML data and convert it into valid format for the database and underlying system. Later, databases started to support XML so that no conversion was […]