> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Pivot
> Convert your table from long to wide format
export const gemName_0 = "Pivot"
export const execution_engine_0 = "the SQL warehouse"
This gem runs in {execution_engine_0}.
## Overview
Use the Pivot gem to convert your table from long format to wide format.
* Select a column whose unique values will become new columns in the output.
* Each unique value in this column creates a new column in your result table.
This helps you summarize and analyze data more easily, letting you see multiple categories side by side.
The {gemName_0} gem has a corresponding interactive gem example. See [Interactive gem
examples](/data-analysis/gems/gems#interactive-gem-examples) to learn how to run sample pipelines
for this and other gems.
## Input and Output
The Pivot gem uses the following input and output ports.
| Port | Description |
| ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **in0** | The source table containing data in long format that needs to be transformed to wide format. |
| **out** | The output table in wide format containing:
• All original grouping columns preserved as row identifiers
• New columns created from unique values in the pivot column and defined aggregations
The output will typically have fewer rows but more columns than the input. |
## Parameters
Configure the Pivot gem using the following parameters.
### Pivot Column Settings
Choose a pivot column that contains the unique values that will become new columns in the output table. Each distinct value from this column that you define will be transformed into a separate column header in the wide format result.
In the **Unique Pivot Column Values** table, you must explicitly define which unique values from the pivot column you want to transform into new columns. String values must be wrapped in quotes.
> Example: If your pivot column contains dates, you can specify `'2025-01-01'`, `'2025-02-01'`, and `'2025-03-01'` to create columns for those dates.
### Row Grouping Settings
The pivot operation will group your data by these columns before applying the aggregation. You can select one or more columns for grouping.
> Example: If you're analyzing sales data and want to see performance by region and product category, you would select both `Region` and `Product_Category` as grouping columns.
### Values
Choose a column that contains the data values that will populate the cells of the new pivot columns. The values from this column will be aggregated according to your chosen method(s) and placed at the intersection of each group and pivot column combination.
Once you choose a column, a table appears where you can define your aggregations. Under **Method for Aggregating Values**, choose methods such as `AVG`, `SUM`, `COUNT`, `MIN`, and `MAX`.
For each aggregation, you can define an optional **Column Alias** that gets appended to the new columns' names. This helps make your output columns more descriptive and meaningful.
> Example: If your pivot column values are dates and you're calculating averages, you might use `AVG_Sales` as the alias, resulting in column names like `2025-01-01_AVG_Sales` and `2025-02-01_AVG_Sales`.
When using the `CONCAT` aggregation method, the **Separator** parameter specifies the character or
string used to separate the values that are being concatenated together. Common separators include
commas, semicolons, and pipes.
## Example: Energy consumption
Imagine you have a dataset of energy consumption with `Date`, `City`, and `kWh_Used` for different buildings, and you want to see the average `kWh_Used` per city for a specific date, with dates as columns. You will start with the following information:
| Date | Building\_ID | Building\_Type | City | Energy\_Source | kWh\_Used | Cost | Peak\_Demand\_kWh |
| ---------- | ------------ | -------------- | ----------- | -------------- | --------- | ----- | ----------------- |
| 2025-07-01 | B001 | Office | New York | Solar | 150 | 30.00 | 10 |
| 2025-07-01 | B002 | Residential | Los Angeles | Grid | 200 | 40.00 | 12 |
| 2025-07-01 | B003 | Retail | New York | Grid | 180 | 36.00 | 11 |
| 2025-07-02 | B001 | Office | New York | Solar | 160 | 32.00 | 10.5 |
| 2025-07-02 | B002 | Residential | Los Angeles | Grid | 210 | 42.00 | 12.5 |
| 2025-07-03 | B004 | Commercial | Chicago | Grid | 250 | 50.00 | 15 |
| 2025-07-03 | B005 | Industrial | Houston | Solar | 300 | 60.00 | 18 |
| 2025-07-04 | B001 | Office | New York | Solar | 155 | 31.00 | 10.2 |
| 2025-07-04 | B003 | Retail | New York | Grid | 185 | 37.00 | 11.5 |
| 2025-07-05 | B002 | Residential | Los Angeles | Grid | 205 | 41.00 | 12.3 |
In the Pivot gem configuration:
1. Set the **Pivot column** to `Date`.
2. Enter the following **Unique Pivot Column Values**: `'2025-07-01'`, `'2025-07-02'`, `'2025-07-03'`, `'2025-07-04'`, and `'2025-07-05'` (or the specific dates you are interested in).
3. For **Group data by these columns**, select the `City` column.
4. For **Values for New Columns**, select the `kWh_Used` column.
5. Under **Method for Aggregating Values**, select `AVG`.
6. For the `AVG` aggregation, add the Column Alias: `AVG_kWh`
### Result
The output table shows the average `kWh_Used` for each city on the specified dates, with the dates as new columns.
| City | 2025-07-01\_AVG\_kWh | 2025-07-02\_AVG\_kWh | 2025-07-03\_AVG\_kWh | 2025-07-04\_AVG\_kWh | 2025-07-05\_AVG\_kWh |
| ----------- | -------------------- | -------------------- | -------------------- | -------------------- | -------------------- |
| New York | 165 | 160 | null | 170 | null |
| Los Angeles | 200 | 210 | null | null | 205 |
| Chicago | null | null | 250 | null | null |
| Houston | null | null | 300 | null | null |
Note that there are some null values in this output table. This is because for certain combinations of `City` and `Date`, there's no corresponding data in the original input table.
The Pivot gem works by taking unique values from the **Pivot Column Settings** (`Date` in this case) and turning them into new columns. It then populates these new columns with aggregated values from the **Values for New Columns** (`kWh_Used`) per group (`City`).
If a specific city does not have any `kWh_Used` data for a particular date, then when that date becomes a column, the corresponding cell will be null. In this example, you can see that Chicago has no data for `2025-07-01`, so the cell is null for that date in the output. In summary, the null indicates the absence of data for that specific intersection of city and date in your original dataset.