Skip to main content

How to UNPIVOT

You may have a use case that requires unpivoting. Unpivoting rotates a table by transforming columns into rows. UNPIVOT is a relational operator that accepts two columns, along with a list of columns, and generates a row for each column specified in the list. For example:

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

In Coalesce, UNPIVOT 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 UNPIVOT SQL as detailed in Snowflake’s UNPIVOT documentation under both approaches.

UNPIVOT Using a Stage Node​

To implement an UNPIVOT in Coalesce via a Stage Node, complete the following steps:

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

  2. In the DAG, Right click on the Node that you will perform the UNPIVOT on and select Add Node > Stage.

  3. In the Mapping tab of the Node Editor for the Stage, re-configure your column names, transforms, data types, and sources to reflect what will be returned by the UNPIVOT statement. This should include keeping your row columns, in order, deleting all of the columns that you will UNPIVOT, and adding columns for the column values and metrics you will be UNPIVOTing. 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 via the JOIN configuration described in Step 4. For example:

    1. If you have a sourcing Node with the structure pictured below.

      The image displays the STG_MONTHLY_SALES table mapping. The table includes columns for STORE, JAN, FEB, MAR, and APR, with corresponding data types and sources. All columns are nullable, and the source for each column is the MONTHLY_SALES table.

    2. And you wish to UNPIVOT the individual month columns into a single month column, you would configure your Stage Mapping as pictured below.

      The image displays the STG_MONTHLY_SALES table mapping after transformation. The table now includes columns for STORE, MONTH, and AMOUNT, with respective data types and transformation rules. All columns are nullable, and the data source for each column is indicated as STORE, MONTH, and AMOUNT respectively.

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

    The image displays the SQL query for unpivoting the STG_MONTHLY_SALES table. The query retrieves data from the MONTHLY_SALES source table and applies an UNPIVOT operation to transform the columns JAN, FEB, MAR, and APR into rows under the columns AMOUNT and MONTH. This query aims to reorganize the data structure by turning multiple month columns into a single column with corresponding month values.

  5. Within the Node Editor:

    1. Validate Select.
    2. Validate Create.
    3. Create.
    4. Validate Run.
    5. Fetch data, and view unpivoted results.
    The image displays the results of an UNPIVOT operation on the STG_MONTHLY_SALES table. The resulting table includes columns for STORE, MONTH, and AMOUNT. Each row represents a specific store's sales amount for a particular month, showing data for Store 1 and Store 2 across the months of January (JAN), February (FEB), March (MAR), and April (APR).

  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 data flow diagram, illustrating the process from the MONTHLY_SALES source table to the STG_MONTHLY_SALES staging table and finally to the FCT_MONTHLY_SALES fact table. The data flows sequentially through these stages, indicating the transformation and loading steps involved in processing the monthly sales data.

UNPIVOT Using a View Node With Override Create SQL​

To implement an UNPIVOT in Coalesce via a View Node with Override Create SQL enabled, complete the following steps:

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

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

  3. Right click on the Node that you will perform the UNPIVOT 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 *. If your SQL is like Figure A, then you can replace your column list with an asterisk *.

      The image shows two SQL scripts for creating or replacing the V_MONTHLY_SALES view. Figure A defines specific columns (STORE, JAN, FEB, MAR, APR) and their respective aliases, sourcing data from the MONTHLY_SALES table. Figure B selects all columns using the asterisk (*) from the MONTHLY_SALES table, without explicitly defining each column.

    2. Update your join to include your UNPIVOT clause after your FROM clause. For the example detailed above, this UNPIVOT clause would be UNPIVOT(AMOUNT FOR MONTH IN (JAN,FEB,MAR,APR)) as pictured in the example below.

      The image shows the SQL script for creating or replacing the V_MONTHLY_SALES view using an UNPIVOT operation. The script retrieves data from the MONTHLY_SALES source table and applies the UNPIVOT operation to convert the columns JAN, FEB, MAR, and APR into rows under the columns AMOUNT and MONTH. This transformation is part of the Override Create SQL stage, allowing for the customization of the view creation process.

  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 for changes. The dialog lists columns with their Before and After states, indicating modifications to the schema. Columns JAN, FEB, MAR, and APR are being removed, while new columns MONTH and AMOUNT are added, reflecting the UNPIVOT operation. The STORE column remains unchanged.

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

    The image shows the updated V_MONTHLY_SALES table mapping. The table now includes columns for STORE, MONTH, and AMOUNT, with their respective data types and sources. All columns are nullable, and the STORE column sources its data from the MONTHLY_SALES table. This updated schema reflects the changes made through the UNPIVOT operation, transforming multiple monthly columns into a single MONTH column with corresponding AMOUNT values.

  8. Within the Node Editor:

    1. Validate Select.
    2. Validate Create.
    3. Create
    4. Fetch data, and view unpivoted results.
    The image displays the final results of the V_MONTHLY_SALES view after the UNPIVOT operation. The table includes columns for STORE, MONTH, and AMOUNT, with data showing sales amounts for Store 1 and Store 2 across the months of January (JAN), February (FEB), March (MAR), and April (APR). This view represents the transformed data where each month's sales for each store are presented in individual rows, making the data more accessible for analysis.

  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 data flow diagram illustrating the process from the MONTHLY_SALES source table to the V_MONTHLY_SALES view and finally to the FCT_MONTHLY_SALES fact table. This diagram indicates the sequence of transformations applied, including the UNPIVOT operation, and the flow of data through these stages, resulting in the final structured fact table ready for analysis.