Previous Topic

Next Topic

Book Contents

Book Index

Pivot columns

From the Data Display window, clicking on Tools > Pivot will create a tabulation from your results. You can use this to:

What distinguishes Pivot from other forms of data manipulation / display, is that Pivot will create columns in your Pivot table from the values of one of your columns (the pivot column). This is something that is difficult to do with normal SQL.

The following shows an example:


Pivot Column

You need to specify which column is to be your Pivot Column. You can only have a single Pivot Column. The values of the Pivot column in your results become columns in the Pivot table.

Your Pivot column should be one which takes only a small number of distinct values. The Pivot table has a limit of 2000 columns so you should choose a column with a smaller number of distinct values this.

Grouping Columns

There will be one line in your pivot table per unique value of the grouping columns. You can have multiple grouping columns.

Analysis Column

This column contains a numeric value that is to be analysed. The values are placed in the analysis grid. If you have multiple rows in your results for each combination of Pivot and Grouping Columns, the values of the analysis column will be summed.

The analysis column is optional. There will be no analysis column if you click on No Analysis Column, or your results have no numeric column. In this case, the grid will be populated with the number of occurrences of the Pivot Column / Grouping Column(s) combination. The example at the end of this topic shows this option being used.

AQT will calculate row and column totals in the grid.

Sort Pivot columns?

When this option is selected, AQT will display the Pivot columns in alphabetic order (or, if the Pivot column is numeric, in numeric order). When this is not selected, AQT will show the pivot columns in the order in which is finds the values (this may seem to be a "random" order).

Show Sample

When you click on this, AQT will show you a sample of your pivot table. It will do this by reading a subset (first 1000 rows) of your data. This is useful for seeing what the Pivot table will look like. Note that it will not display the full amount of data or the full number of Pivot columns.

Saving / Loading a Pivot Spec

This can be done with Save/Load Pivot options. This if useful if you are running a certain Pivot on a regular basis.

Generate SQL

When this button is clicked, AQT will generate an SQL statement that will create a result-set similar to the Pivot table. Note that AQT itself doesn't use this SQL statement - it creates the Pivot results by processing the data in the grid.

Other notes

Advanced Query Tool
© 2023 Cardett Associates Ltd. All rights reserved.