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

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 unpivotregister 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 corresponding 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 Eugene L Walter C080 27 CA Melisa Lim C040 Oakland

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

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.