How to Pivot and Unpivot views

Applies to: Denodo 8.0 , Denodo 7.0 , Denodo 6.0
Last modified on: 03 Dec 2020
Tags: Combination View creation

Download document

You can translate the document:

Goal

This document describes how to apply a row to column (pivot) or a column to row (unpivot) transformation in a Denodo Virtual DataPort view.

Note

Starting with version 20201124, new functions have been added to the Denodo Xtrafuncs for VDP that allow to perform pivot and unpivot operations

The functions pivotregister transform input array values to columns.

The function unptivotregister transforms an input record to rows.

For more information on how to use these functions see the Denodo XtraFuncs - User Manual.

Content

This document is composed of two sections:

  • Pivot: explains how to transform a row to a column using an example.
  • Unpivot: explains how to transform a column to a row with an example.

  1. Pivot

In the following view df_jobs:

JOB

DEPTNO

COUNT

ANALYST

20

2

CLERK

10

1

CLERK

20

2

CLERK

30

1

MANAGER

10

1

MANAGER

20

1

MANAGER

30

1

PRESIDENT

10

1

SALESMAN

30

4

we want the different department numbers under DEPTNO to be columns. In order to do that:

  • We have 4 department numbers that we want to use: 10, 20, 30 and 40.  

  • We are going to create one column for each department dept_10, dept_20, dept_30, dept_40 that will have the values that are currently in the COUNT column.  

  • The VQL query to create the new view will look like this:
  • CREATE VIEW p_df_jobs AS SELECT job AS job,

max(case WHEN (deptno = '10') THEN count ELSE NULL END) AS dept_10,

max(case WHEN (deptno = '20') THEN count ELSE NULL END) AS dept_20,

max(case WHEN (deptno = '30') THEN count ELSE NULL END) AS dept_30,

max(case WHEN (deptno = '40') THEN count ELSE NULL END) AS dept_40 FROM df_jobs

GROUP BY job

  • It is possible to create the same view in using the graphical interface from the VDP admin tool instead of using the VQL query directly:
  • Grouping the result set to have all the attributes of each item (Group By tab):

  • Adding the 4 fields in the Output tab with the following function using the correct deptno for each field. For example:
  • dept_40 
  • max(case WHEN (df_jobs.deptno = '10') THEN count ELSE NULL END)

And the results:

Before Pivot

After Pivot

This new view has pivoted the COUNT column by DEPTNO across JOB.

Another typical use case is when you have key-value data sources, i.e., for a given register natural key you have multiple records, each one with a key and its correspondent value.

Name

Surname

ID

KEY

VALUE

John

Smith

C001

age

32

John

Smith

C001

city

San Jose

Eugene L

Walter

C080

age

27

Eugene L

Walter

C080

state

CA

Melisa

Lim

C040

city

Oakland

In this case, we want a new column for each one of the possible values of ‘key’. The pivoted table could be created as:

CREATE VIEW pivoted_view AS SELECT name AS name, surname AS surname, id as id,

max(case WHEN (key = 'age') THEN value ELSE NULL END) AS age,

max(case WHEN (key = 'city') THEN value ELSE NULL END) AS city,

max(case WHEN (key = 'state') THEN value ELSE NULL END) AS state FROM my_view

GROUP BY name, surname, id;

And the result would be:

Name

Surname

ID

Age

City

State

John

Smith

C001

32

San Jose

<null>

Eugene L

Walter

C080

27

<null>

CA

Melisa

Lim

C040

<null>

Oakland

<null>

  1. Unpivot

In the following view df_customer_phone:

customer_id

phone1

phone2

phone3

1

705-421-4577

705-421-1421

2

734-843-9756

372-626-7333

705-521-9242

3

834-277-1151

263-421-7326

4

238-843-4734

256-421-2000

522-421-1421

5

125-341-1111

632-346-1521

we want to have two columns, one with the customer_id and one with a single phone. The approach is to create a union with a branch for every column that we are going to unpivot. In this case the columns “phone1”, “phone2” and “phone3” will be transformed to the column “phone” in our unpivoted view.  

The easiest way to do this is to create the view manually using VQL. In other cases we may need to create a projection view for every branch and then create the view using the graphical interface:

CREATE VIEW unpivot_df_customer_phone AS

SELECT customer_id, phone1 as phone FROM df_customer_phone WHERE phone1 <> ''

UNION

SELECT customer_id, phone2 as phone FROM df_customer_phone WHERE phone2 <> ''

UNION

SELECT customer_id, phone3 as phone FROM df_customer_phone WHERE phone3 <> ''

Note: In this case, we are using phoneX <> ’’ as the WHERE condition in the different union branches because we are using a delimited file as data source. ‘phoneX is not null’ may be needed in other cases.

To create this view using the GUI:

  • Create a projection view for each branch; maintain customer_id and one of the phone columns for each view.

  • Every view will have a WHERE condition for empty values applying to the same column that we are projecting on the view. The following screenshots from the Virtual DataPort Administration Tool show the projection returning only the phone3 column; two more views need to be created for this example: one returning phone1 and one returning phone2.

  • Where Condition:

  • Output:

  • Create an Union View using the projection views and include an association for the phoneX fields:

In both cases using the GUI and using VQL the final view will look like this:

And the results of executing the query will be:

 

Before unpivot

After unpivot

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training