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:

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.

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

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.

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).

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.

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

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.

- 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.