Pivot columns

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

• display your data in a way that is easier to read
• summarise your data

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.

• the SQL statement will not generate the Total row and column.
• the Pivot columns in the SQL statement will based on the values of the Pivot column currently displayed in the grid. If the contents of the table changes, you may need to re-generate the Pivot SQL statement.
• the SQL statement has a column Other, which contains values of the pivot column which aren't present in the Pivot columns. This is useful if your data changes after you have generated the SQL statement. Once you start seeing data appearing in the Other column, it is time to regenerate the Pivot SQL.
• this SQL statement may not necessarily run on all databases. The SQL uses the CASE statement which isn't support on all databases.

Other notes

• when you run Pivot, AQT will sort your source grid into the grouping-columns order. This is necessary for the way AQT processes the data in the grid.
• Pivot cannot be run in batch. However, Generate SQL provides you with an query that produces the pivot data. This query can be used in a batch script.