Lay Out and Sort Query Results

By default, query results are flat and unsorted. To ease analysis, you can lay out results as a cross-tabulation or sort flat results by multiple fields.

From the Layout menu, select whether to lay out the results as flat or crosstab:

• Flat —By default, query results appear flat, with all fields as separate columns, like a table. 
• Crosstab —To help compare and analyze data, use a cross-tabulation, or crosstab, layout. Crosstab groups the results horizontally and vertically based on dimensions you choose. To further help summarize the data, you can also apply calculations—such as COUNT or MAX—to values.

For example, a query's default results provide sales data in a flat layout:

With crosstab results, you can lay out the same data to compare each region's total sales during each fiscal year at a glance:

From the Sort tab, you can sort flat results in ascending or descending order by multiple fields. 

Lay Out Crosstab Results in the Query Builder

To ease the analysis of categorical data, you can lay out the query's fields for cross-tabulation when in the query builder.

1. In the query, add the source fields and calculations to include in the results.
2. From the toolbar, select Layout , Crosstab. 
   
3. On the Fields tab, specify how to display the fields in the results:
   
   
   • For values to display vertically, such as Region in our example, leave its field under Rows.
   • For values to display horizontally, such as Fiscal Year in our example, move its field from Rows to Columns.
   • To apply a calculation to a value within the comparison, move its field from Rows to Values, and select the formula to use such as DISTINCTCOUNT or SUM.

   Note: To remove a row from the results, delete its field from the query.

4. To view the results in the crosstab layout, click Run Query .
5. Click Save .

Lay Out Crosstab Results in the SQL Editor

From the SQL editor, you can lay out the query results as a crosstab to ease the analysis of categorical data.

Note: You can set up a crosstab layout only when the SQL is valid.

1. In the editor, compose the query's SQL, and verify no syntax errors occur.
2. For each field in the SELECT statement, use an AS statement to specify its header in the crosstab layout, including if the same as the field's name. For example, SELECT field_name1 AS "alias", field_name2 AS "field_name2" FROM source_name.
3. In the Layout right panel, select Enable Crosstab, and specify how to display the fields in the results:
   
   
   • For values to display vertically, such as Region in our example, leave its field under Rows.
   • For values to display horizontally, such as Fiscal Year in our example, move its field from Rows to Columns.
   • To apply a calculation to a value within the comparison, move its field from Rows to Values, and select the formula to use such as DISTINCTCOUNT or SUM.

   Note: To remove a row from the results, delete its field from the query.

4. To preview the results in the crosstab layout, click Run Query .
5. Click Save .

Lay Out Flat Results

By default, query results appear in a flat layout. To return to a flat layout after setting up crosstab results, select Layout , Flat from the toolbar.

Sort Flat Query Results

In Builder mode, you can sort flat query results in ascending or descending order according to one or more fields from the Sort tab:

1. Move each field to sort according to from Unsorted to Sorted, and select whether to sort its data in ascending or descending order.
2. If you move multiple fields to Sorted, arrange them in the order to sort their data by. For example, you may want to sort first in ascending order according to one field, then in descending order according to another.
3. Click Save .

In SQL mode, use an ORDER BY keyword to sort the results in ascending (ASC) or descending (DESC) order, such as ORDER BY field_header ASC. To sort according to multiple fields, separate each with a comma, such as ORDER BY field_header1 ASC, field_header2 DESC.