<!--
By default, query results are flat and unsorted. To ease analysis, you can lay out results as a cross-tabulation or sort results by multiple fields.
-->預設情況下，查詢結果是條列式且未排序的。為協助比較和分析資料，您可以：
- 根據一個或多個欄位，以遞增或遞減方式排序結果。
- 使用交叉表—或稱「交叉資料表」—版面配置，根據您選擇的維度以水平和垂直方式將結果分組，並將彙總值（例如 COUNT 或 MAX）套用於行或列。
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.
例如，查詢結果預設會以平面表格版面配置提供銷售資料：
| 區域 | 銷售額 | 財務年度 |
|---|---|---|
| 北部 | 100 | 2020 |
| 北部 | 50 | 2020 |
| 南 | 100 | 2019 |
| 南 | 250 | 2020 |
| 東部 | 200 | 2019 |
| 東部 | 50 | 2019 |
| 西 | 100 | 2019 |
| 西 | 100 | 2020 |
使用交叉資料表結果，您可以排列資料並加以摘要，以比較各地區各財務年度的總銷售額：
| 區域 | 2019 | 2020 |
|---|---|---|
| 東部 | 250 | 0 |
| 北部 | 0 | 150 |
| 南 | 100 | 250 |
| 西 | 100 | 100 |
排列交叉資料表結果
為了簡化對分類資料的分析，您可以將結果排列為交叉資料表，並在任一建立器或 SQL
- 在查詢中，新增要包含在結果中的欄位：
- 在 Builder 中，將來源欄位和計算方式新增到 Fields 索引標籤。
- 在 SQL 中 ，編寫查詢的 SQL，並驗證是否發生語法錯誤。為
SELECT陳述式中的每個欄位使用AS陳述式，以在交叉分析版本配置中指定其標題（即使與欄位的名稱相同）。例如，SELECT field_name1 AS "alias", field_name2 AS "field_name2" FROM source_name。
- 在 Crosstab 面板中，選取 Enable Crosstab，並指定如何在結果中顯示欄位：
- 針對要垂直顯示的值，如本例中的 Region，請在 Rows 下方留下其欄位，並選取要以遞增或遞減方式排序其資料。
- 針對要水平顯示的值，如本例中的 Fiscal Year，請將其欄位從 Rows 移至 Columns，並選取要以遞增或遞減方式排序其資料。
- 若要在比較中套用值的計算，請將其欄位從資料列移動至值，並選取要使用的彙總，如 DISTINCTCOUNT 或 SUM。若要將值顯示為總計，請在「總計」中選取應該為資料列還是資料欄顯示該值。
注意：要從結果中移除資料列，請從查詢中刪除其欄位。
- 若要在跨表格版面配置中檢視結果，請按一下「執行查詢」(Run Query) 。
- 按一下「儲存」。
<!--
- In the query, add the source fields and calculations to include in the results.
-
From the toolbar, select Layout
, Crosstab.
-
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.
- To view the results in the crosstab layout, click Run Query .
- Click Save .
- In the query, add the source fields and calculations to include in the results.
-
From the Layout
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, and select whether to sort its data in ascending or descending order.
- 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 aggregation to use such as DISTINCTCOUNT or SUM. To display the value as a grand total, in Totals, select whether it should appear for rows or columns.
Note: To remove a row from the results, delete its field from the query.
- To preview the results in the crosstab layout, click Run Query .
- Click Save .
- In the editor, compose the query's SQL, and verify no syntax errors occur.
-
For each field in
the
SELECTstatement, use anASstatement 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. -
From the Layout
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. To display the value as a grand total, in Totals, select whether it should appear for rows or columns.
Note: To remove a row from the results, delete its field from the query.
- To preview the results in the crosstab layout, click Run Query .
- Click Save .
Include grand totals in crosstab results
To help summarize values in crosstab results, you can include grand totals for columns or rows in either Builder or SQL:
- When you set up the crosstab layout, drag the field with the values to calculate from Rows to Values, and select the aggregation to use, such as DISTINCTCOUNT or SUM.
-
From the toolbar, click Totals, and select whether to show
the grand totals for rows or columns.
<!-- - Click Save .
排列平面結果
<!--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.
預設情況下，查詢結果以條列式版面配置顯示。要將交叉分析結果傳回條列式版面配置，請從「版面配置」面板中取消選取「啟用交叉分析」。
排序平面結果
依預設不會排序顯示的查詢結果。在建立器或 SQL 中，您可以根據一個或多個欄位依升序或降序對結果排序。
- 在「排序」(Sort) 標籤標籤中，將每個欄位從「未排序」(Unsorted) 移動至「排序」(Sorted)，然後選擇以升序或降序方式排序資料。
- 如果根據多個欄位排序，請在「已排序」下按資料排序順序排列這些欄位。例如，您可能希望先根據一個欄位依升序排序，然後根據另一個欄位依降序排序。
- 按一下「儲存」。