Pivoting: pivot

Syntax

op [ pivot identifier , measure ]

Input parameters

op	the operand
identifier	the Identifier Component of op to pivot
measure	the Measure Component of op to pivot

Examples of valid syntaxes

DS_1 [ pivot  Id_2,  Me_1 ]

Semantics for scalar operations

This operator cannot be applied to scalar values.

Input parameters type

op

dataset

identifier

name<identifier>

measure

name<measure>

Result type

result

dataset

Additional Constraints

The Measures created by the operator according to the behaviour described below must be defined on the same Value Domain as the input Measure.

Behaviour

The operator transposes several Data Points of the operand Data Set into a single Data Point of the resulting Data Set. The semantics of pivot can be procedurally described as follows.

It creates a virtual Data Set VDS as a copy of op
It drops the Identifier Component identifier and all the Measure Components from VDS.
It groups VDS by the values of the remaining Identifiers.
For each distinct value of identifier in op, it adds a corresponding measure to VDS, named as the value of identifier. These Measures are initialized with the NULL value.
For each Data Point of op, it finds the Data Point of VDS having the same values as for the common Identifiers and assigns the value of measure (taken from the current Data Point of op) to the Measure of VDS having the same name as the value of identifier (taken from the Data Point of op).

The result of the last step is the output of the operation.

Note that pivot may create Measures whose names are non-regular (i.e. they may contain special characters, reserved keywords, etc.) according to the rules about the artefact names described in the User Manual (see the section “The artefact names” in the chapter “VTL Transformations”). As said in the User Manual, those names must be quoted to be referenced within an expression.

Examples

Given the Data Set DS_1:

Input DS_1 (see structure)

Id_1	Id_2	Me_1	At_1
1	A	5	E
1	B	2	F
1	C	7	F
2	A	3	E
2	B	4	E
2	C	9	F

Example 1

DS_r := DS_1 [ pivot Id_2, Me_1 ];

results in (see structure):

DS_r
Id_1	A	B	C
1	5	2	7
2	3	4	9