Versions Compared

Old Version 18

changes.mady.by.user Anand Vedam

Saved on

compared with

New Version Current

changes.mady.by.user Gouri Bhagchandani

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

Table of Contents
maxLevel2
absoluteUrltrue

Multiexcerpt include
templateData[]
MultiExcerptNameME_OE_ELT_Snap_Accounts
addpanelfalse
PageWithExcerptELT Aggregate

Overview

An UNPIVOT operation converts columns into rows. You can use this Snap to add the UNPIVOT 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 the Snap supports them internally through a series of rewrite mechanisms.

Prerequisites

None.

Limitation

Multiexcerpt include macro
nameME_ELT_GBQ_StandardSQL_UOI
pageELT Copy

Known Issues

Multiexcerpt include macro
nameME_ELT_KI_Underscore_Refs
pageELT Snap Pack

Snap Input and Output

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

Document

  • Min: 1
  • Max: 1
  • ELT Select
  • ELT Transform
The SQL query in which to add the UNPIVOT function. Typically, it is a SELECT query reading the source table.
Output

Document

  • Min:
  • Max:
  • ELT Insert-Select
  • ELT Filter

The incoming SQL query with the UNPIVOT function.

Snap Settings

Info
titleSQL Functions and Expressions for ELT

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
Insert excerpt
File Writer
File Writer
nopaneltrue
ELT UnpivotSales Data
Get preview dataCheckbox
Multiexcerpt include macro
namegetpreviewdata
pageELT Intersect
Not selectedSelected
Value ColumnStringRequired. Enter the name for the generated column that shows the values corresponding to the columns specified in the Column List field set. N/ASales
Name ColumnStringRequired. Enter the name for the generated column that shows the names corresponding to the columns specified in the Column List field set. N/AMonth

Column List

Required. Use this field set to specify the columns in the source table to convert into rows. You must add each column in a separate row. Click  to add a row.

This field set contains the Column field.

ColumnStringRequired. Enter the names of the columns to convert into rows.N/A

Jan

Feb

Mar

Troubleshooting

None.

Example

Consolidating Quarterly Sales of an Employee

We want to consolidate the quarterly sales of an employee. The source table that captures the sales data is organized by month, meaning that the employee's sales are recorded in a single row, where the months are organized as columns. We need to first convert the column data into row data and then write this modified data into another table. This Pipeline demonstrates how we can use the ELT Unpivot 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 SALES_BY_3_MONTHS. We want to retrieve the sales data for the employee with ID 1. 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:
Image Modified

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

As seen from the above screenshot, the sales data is organized by months. Each month is a separate column. Therefore, we use the ELT Unpivot Snap and configure it to change the columns into rows as shown below:
Image Modified

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

Based on the ELT Unpivot Snap's configuration, the month-based columns are turned into rows under a new column MONTH. Similarly, the sales amounts for each of these months are placed under the AMOUNT column.

We use an ELT Insert-Select Snap to write the above output into another table as shown below:
Image Modified

We can now use the data in the new table SALES_BY_3_MONTHS_UNPIVOT_OUT to conduct further analysis and processing.

Download this Pipeline.

Downloads

Note
titleImportant Steps to Successfully Reuse Pipelines
  1. Download and import the Pipeline into SnapLogic.
  2. Configure Snap accounts as applicable.
  3. Provide Pipeline parameters as applicable.

Attachments
patterns*.slp, *.zip

Insert excerpt
ELT Snap Pack
ELT Snap Pack
nopaneltrue

See Also