Cross Tab Tool
Use Cross Tab to pivot the orientation of data in a table by moving vertical columns onto a horizontal axis and summarizing data where specified.
The Cross Tab tool is the reverse application of the Transpose Tool, which pivots the orientation of the data from horizontal to vertical. There is no limit to the number of rows or columns that you can pass through Cross Tab.
If 2 or more cells contain the same string with different capitalization, a number is appended to the string name after the first instance. For example, ColumnID and columnId become ColumnID and columnId2.
Tip
This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.
Configure the Tool
Working in Live Query for Google BigQuery?
In Live Query for Google BigQuery, the Cross Tab tool behaves differently:
The tool dynamically pivots columns based on the dataset metadata.
The New Column Names field is removed.
Go to the Cross Tab (Live Query for Google BigQuery) page for details and limitations.
Select a column from Create New Column From. A new column is created for each unique value.
Select your New Column Names. Select Reset to reset your selected column names.
Select a column from Populate with Values From. The values from this column are used to populate the new columns.
Select 1 or more Output Method for combining multiple values in a column. Each method selected appears as a new column in your data. For string data, specify these parameters after Output Method:
Separator: Enter a character to separate multple values when concatenating strings. The character specified is automatically added between concatenated strings.
Character Length: Enter a max character length for the output. If a string in the data is larger than the specified length, a warning is shown in the Results Grid and the data is truncated.
(Optional) Select Columns to Group By. These columnds define row-level grouping.
Tool Behavior in Google BigQuery
The Cross Tab tool in Live Query for Google BigQuery generates pivot columns dynamically from dataset metadata. The tool dynamically derives pivot columns directly from dataset metadata, rather than being manually specified.
Metadata Behavior
Because the tool runs in Live Query:
New pivot values result in new output columns.
Renamed pivot values update automatically.
Removed values are no longer included.
This reduces workflow maintenance in environments where source data changes frequently.
Note that metadata changes aren't automatically propagated to downstream Cross Tab tools placed after the initial tool.
Limitations
These limitations apply:
You can’t pivot Date, DateTime, or Float data types.
You can’t pivot…
Cells that contain unsupported characters.
Values longer than 300 characters.
Cells with duplicate values.
Maximum number of pivoted columns is 999.
The Use First and Use Last output methods for String columns aren’t available.
Adjust upstream data if your dataset exceeds these constraints.