Overview

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: 

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

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

• Via a Stage Node
• Via a 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 via a Stage Node

To implement a pivot 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 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, ie: 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.
   2. If you wish to pivot AMOUNT on MONTH and group by STORE, you'd reconfigure your stage as pictured:
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.
5. Within the Node Editor:
   1. Validate Select. Confirm success or troubleshoot upon failure.
   2. Validate Create. Confirm success or troubleshoot upon failure.
   3. Create
   4. Validate Run. Confirm success or troubleshoot upon failure.
   5. Fetch data, and view pivoted results
6. Proceed to add the dependent downstream Node(s) for your data pipeline, whether it be direct to target or additional processing Node(s). For example:

Pivot via a View Node with Override Create SQL

To implement a pivot 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 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.

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:
   2. I would modify it by replacing my column list of STORE AS STORE, AMOUNT AS AMOUNT, MONTH AS MONTH with a simple asterisk (*) as pictured below.
   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.
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.
7. Navigate back to the mapping tab, and confirm the column changes and mappings have taken effect.
8. Within the Node Editor:
   1. Validate Select. Confirm success or troubleshoot upon failure.
   2. Validate Create. Confirm success or troubleshoot upon failure.
   3. Create
9. Proceed to add the dependent downstream Node(s) for your data pipeline, whether it be direct to target or additional processing Node(s). For example: