Apollo SQL > Using Apollo SQL > Using Cross-tab Queries to Present Summary Data

People are accustomed to viewing summary data in the row-column format of accountants' worksheets. Worksheets that are used for comparative performance analysis fall into two basic categories:

•Time-series format implies that column titles contain date intervals, such as months, quarters, or years, and that row titles designate the category of data being compared. The underlying detail data is grouped by both data category and time period. Time-series summary data often is used to prepare line graphs or bar charts with sales as the y-axis (vertical axis or abscissa) and time as the x-axis (horizontal axis or ordinate).

•Classification format uses column titles with the names of individuals, regions, divisions, or other organizational categories and data categories for the row titles. This format is restricted to a single, predetermined time period. (Multiple time periods can be represented by "stacking" worksheets with an identical format that can be consolidated by adding the values of corresponding cells.) The most common graphical representation of data from classification worksheets is the pie chart.

Today's spreadsheet applications, such as Microsoft Excel, replace the drudgery of preparing handwritten worksheets with automated computer-based procedures. However, the majority of the detail information that is needed to prepare summary data for management is available only in the fields of tables of relational accounting databases. Conventional SQL statements return data in relational format, not spreadsheet format. Thus, a substantial amount of data manipulation ordinarily is required to create a time-series or classification spreadsheet from relational data.

The following sections describe how summary data is returned by SQL and how you use Apollo SQL's TRANSFORM and PIVOT statements to automate the denormalization of relational data to spreadsheet format.