Skip to main content

How to PIVOT

You may have a use case that requires pivoting. Pivoting rotates a table by turning the unique values from one column in the input expression into multiple columns and aggregating results where required on any remaining column values. For example:

The image illustrates the transformation of a data table using a pivot operation. The original table lists stores, amounts, and months in a vertical format, while the pivoted table reorganizes the data horizontally, summarizing the amounts for each store by month. This pivot table provides a more consolidated view, making it easier to compare monthly sales across different stores.

See Snowflake’s PIVOT documentation for more information on this functionality.

In Coalesce, PIVOT can be achieved via two differing approaches:

  • Stage Node
  • View Node with the Override Create SQL option enabled in the Node’s configuration

The approach you select will depend on your and your organization's preferences. You will still have to craft the PIVOT SQL as detailed in Snowflake’s PIVOT documentation under both approaches.

Pivot Using a Stage Node​

  1. In the Browser of the Build Interface, ensure you have added the Node (Source, Stage, etc.) that you will perform the pivot on.

  2. Right click on the Node that you will perform the pivot on and select Add Node > Stage.

  3. In the Mapping tab of the Node Editor for the Stage, re-configure your column names, transforms, and data types to reflect what will be returned by the PIVOT statement. This should include your row columns, in order, followed by the pivoted values. Leave the Source for each column blank, and fill in the Transform for each column with the column name as it will be returned from the SQL; note that Snowflake encloses pivoted column names in single quotes, for example, JAN will be named 'JAN' in the SQL results; to correct for this, you will override the returned named with your intended names in the JOIN configuration described in Step 4. For example:

    1. In the Stage pictured below, the results will result-set will return three columns, STORE, AMOUNT, and MONTH.

      The image shows a database interface for a table named STG_MONTHLY_SALES. This table contains three columns: STORE (VARCHAR), AMOUNT (NUMBER), and MONTH (VARCHAR), all of which are marked as nullable

    2. If you wish to pivot AMOUNT on MONTH and group by STORE, you'd reconfigure your stage as pictured:

      The image shows a database interface for a table named STG_MONTHLY_SALES. This table has columns named STORE, JAN, FEB, MAR, and APR, with STORE being a VARCHAR data type and the month columns being NUMBER data types, all marked as nullable.

  4. Navigate to the Join tab of the Node Editor and update your join to include your PIVOT clause after your FROM clause. For the example detailed above, this PIVOT clause would be PIVOT(SUM(AMOUNT) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR')) AS p (STORE, JAN, FEB, MAR, APR) as pictured in the example below.

    The image shows a database query interface for the table STG_MONTHLY_SALES. The SQL query is written to pivot the data, transforming the MONTHLY_SALES table by summing the AMOUNT for each month (JAN, FEB, MAR, APR) and displaying it in a new format with columns for each month.

  5. Within the Node Editor:

    1. Validate Select.
    2. Validate Create.
    3. Create.
    4. Validate Run.
    5. Fetch data, and view pivoted results.
    The image displays the results of executing the pivot query for the table STG_MONTHLY_SALES. The resulting data is shown in a tabular format with columns for STORE, JAN, FEB, MAR, and APR, where each store's monthly sales amounts are displayed.

  6. Proceed to add the dependent downstream Nodes for your data pipeline, whether it be direct to target or additional processing Nodes. For example:

    The image shows a graphical representation of a data flow in a database browser interface. It illustrates the relationship between three tables: MONTHLY_SALES, STG_MONTHLY_SALES, and FCT_MONTHLY_SALES. The flow indicates that data is first processed in MONTHLY_SALES, then transformed and staged in STG_MONTHLY_SALES, and finally loaded into FCT_MONTHLY_SALES.

Pivot Using a View Node With Override Create SQL​

  1. In the Build Settings of your Workspace in the Coalesce app, enable the View Node Type if it is not already enabled for your workspace. This Node Type is disabled by default.

  2. In the Browser of the Build Interface, ensure you have added the Node (Source, Stage, etc.) that you will perform the pivot on.

  3. Right click on the Node that you will perform the pivot on and select Add Node > View.

  4. In the View Node’s configuration settings, toggle on the Override Create SQL option.

    The image shows the V_MONTHLY_SALES view node configuration. The mapping tab displays columns for STORE, JAN, FEB, MAR, and APR with their respective data types and sources. The configuration panel on the right has an option to Override Create SQL, which is enabled, indicating that custom SQL can be used to create the view.

  5. Navigate to the Create SQL tab of the Node Editor, and modify the SQL as follows:

    1. Replace the existing column list with asterisk (*). For example, if my SQL looks like the construct pictured below:

      The image displays a SQL editor interface for creating a view named V_MONTHLY_SALES. The SQL code defines the view by selecting the STORE, AMOUNT, and MONTH columns from the MONTHLY_SALES source table.

    2. You can modify it by replacing my column list of STORE AS STORE, AMOUNT AS AMOUNT, MONTH AS MONTH with a simple asterisk (*) as pictured below.

      The image displays a SQL editor interface for creating or replacing a view named V_MONTHLY_SALES. The SQL code selects all columns from the source table MONTHLY_SALES and defines the view with a simple SELECT * statement.

    3. Update your join to include your PIVOT clause after your FROM clause. For the example detailed above, this PIVOT clause would be PIVOT(SUM(AMOUNT) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR')) AS p (STORE, JAN, FEB, MAR, APR) as pictured in the example below.

      The image shows a SQL editor interface for creating or replacing a view named V_MONTHLY_SALES. The SQL code defines the view by selecting all columns and then pivoting the MONTHLY_SALES table to sum the AMOUNT for each month (JAN, FEB, MAR, APR). The pivoted data is organized into columns for each month.

  6. Still in the Create SQL tab of the View Node Editor, click the Derive Mapping button. This will detect the changes between the Node’s current column configuration vs. those found in the Create SQL you’ve input, and ask you to confirm you wish the changes to take effect. If everything looks correct, click Update.

    The image shows a confirmation dialog box asking if changes should take effect in a database. The dialog lists columns with their statuses before and after the changes: JAN, FEB, MAR, and APR are added as NUMBER(38,0); AMOUNT and MONTH are removed; STORE remains unmodified. Two action steps are highlighted: Validate to confirm the changes and Update to apply them.

  7. Navigate back to the mapping tab, and confirm the column changes and mappings have taken effect.

    The image shows a database interface for the view V_MONTHLY_SALES. This view contains six columns: STORE (VARCHAR), JAN (NUMBER), FEB (NUMBER), MAR (NUMBER), APR (NUMBER), all of which are marked as nullable and sourced from the MONTHLY_SALES table.

  8. Within the Node Editor:

    1. Validate Select.
    2. Validate Create.
    3. Create.

  9. Proceed to add the dependent downstream Nodes for your data pipeline, whether it be direct to target or additional processing Nodes. For example:

    The image shows a graphical representation of the data flow in a database browser interface. It illustrates the relationship between three tables: MONTHLY_SALES, V_MONTHLY_SALES, and FCT_MONTHLY_SALES. The flow indicates that data is first processed in the MONTHLY_SALES table, then transformed and viewed in V_MONTHLY_SALES, and finally loaded into FCT_MONTHLY_SALES