In this article

Overview

A PIVOT operation converts unique values in a specified column into their own individual columns. You can then specify a pivot column and perform aggregate calculations such as sum, count, and average on the values in the column. You can use this Snap to add the PIVOT operation to an incoming SQL query for Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform (DLP), and BigQuery databases. Azure Synapse and Databricks Lakehouse Platform (DLP) do not support Pivot/Unpivot operations natively, but this Snap supports them internally through a series of rewrite mechanisms. 

A typical use of this Snap is categorizing table data, and calculating the performance of sales professionals. 

Prerequisites

None.

Limitation

Known Issues

Snap Input and Output


Input/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
Input 

Document

  • Min: 1
  • Max: 1
  • ELT Transform
  • ELT Select
  • ELT Copy
  • ELT Filter
The SQL query in which you want to add the PIVOT function. Typically, it would be a SELECT query reading the source table. 
Output

Document

  • Min: 1
  • Max: 1
  • ELT Insert-Select
  • ELT Transform
  • ELT Unique

The incoming SQL query with the PIVOT function.

Snap Settings

You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.


Parameter NameData TypeDescriptionDefault ValueExample 
LabelString
ELT PivotPivot Student Data
Get preview dataCheck boxNot selectedSelected
Aggregate FunctionString

Select the aggregate function to apply to the pivot column.

Available options are:

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM
AVGSUM
Pivot ColumnStringRequired. Specify the column in the source table upon which to apply the aggregate functions. N/AORDER_AMOUNT
Value ColumnStringRequired. Specify the column to calculate the aggregate on the pivot columns.N/AAGENT_CODE
Pivot Value List

Required. This field set enables you to specify the values in the Value Column for which you want to see the aggregated values. Specify each value in a separate row. Click + to add a new row.

This field set consists of the following field:

  • Pivot Value

If you do not specify any values in the Pivot Value List table, the system automatically figures out all the values in the Pivot Value Column and adds them to the Pivot Value List. This mode is called Auto Fill Value List. If you specify the values in the Pivot Value List Table, then the current behavior continues to work. This mode is called Manual Fill Value List.

If the Auto Fill Value List option is chosen, the system issues SELECT DISTINCT <value column> FROM < input SQL String> SQL and adds them to local to the Pivot Value List Table which is then subsequently used by the Pivot operation. During validation with the Auto Fill Value List option, the value list SQL would be:SELECT DISTINCT <value column> FROM <input SQL String> ORDER BY <value column> LIMIT <preview limit>. To give consistent output during validation, the ORDER BY clause needs to be added, otherwise, the result during validation would be non-deterministic.


Pivot ValueStringRequired. Enter the list of unique values in the Value Column to include in the output. The column contains null values if the specified value here does not have any records in the input table.N/A

A002

A004

Troubleshooting

Error

Reason

Resolution

SQL Error: An object or column name is missing or empty for SELECT INTO statements. Incorrect syntax near the keyword 'AS'.

Verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Note: the same empty value like '' works fine on SF and DLP.

Examples

Analyzing Sales Agent's Performance

We want to analyze the sales made by two agents in a company. We can construct a query that reads the source table, filters the sales records of these two agents using a WHERE clause, and then add aggregate functions grouped by the agent's identifier. However, this approach only provides us with the aggregated score without further details.
In a sales environment, we typically need additional details such as dates of sale, products sold, and a number of orders. This Pipeline shows how we can use the ELT Pivot Snap to accomplish this task.

First, we build a SELECT query to read the target table. To do so, we use the ELT Select Snap. Sales data is maintained in the table ORDERS3. We configure the ELT Select Snap to read this table. Additionally, we also configure the Snap to show a preview of the SELECT query's execution:

A preview of the output from the ELT Select Snap is shown below:

We want to calculate the total sales made by the agents, A002, and A010, on each day. Therefore, we use the ELT Pivot Snap and configure it as shown below:

This Snap builds the following query based on this configuration:

A tabular representation of this output is as shown below:

The days on which the agents did not make any sales are automatically assigned a null value. We can use an ELT Insert-Select Snap to write the query's output into another table. Additionally, we can use a combination of the ELT Copy and ELT Filter Snaps to write each of the agent's performances into separate tables. 

Download this Pipeline.

Downloads

  1. Download and import the Pipeline into SnapLogic.
  2. Configure Snap accounts as applicable.
  3. Provide Pipeline parameters as applicable.

See Also

Provide links to the endpoint's official documentation, any relevant or related internal documentation, and any other links you have referred to on this page. Care should be taken when referencing external sites/documentation that it is an official site/documentation. Do not refer to forums such as stackoverflow.