Unpivoting: unpivot

Syntax

op [ unpivot identifier , measure ]

Input parameters

op	the operand
identifier	the Identifier Component to be created
measure	the Measure Component to be created

Examples of valid syntaxes

DS_1 [ unpivot   Id_5,  Me_3 ]

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

All the measures of op must be defined on the same Value Domain.

Behaviour

The unpivot operator transposes a single Data Point of the operand Data Set into several Data Points of the result Data set. Its semantics can be procedurally described as follows.

It creates a virtual Data Set VDS as a copy of op
It adds the Identifier Component identifier and the Measure Component measure to VDS
For each Data Point DP and for each Measure M of op whose value is not NULL, the operator inserts a Data Point into VDS whose values are assigned as specified in the following points
The VDS Identifiers other than identifier are assigned the same values as the corresponding Identifiers of the op Data Point
The VDS identifier is assigned a value equal to the name of the Measure M of op
The VDS measure is assigned a value equal to the value of the Measure M of op

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

When a Measure is NULL then unpivot does not create a Data Point for that Measure. Note that in general pivoting and unpivoting are not exactly symmetric operations, i.e., in some cases the unpivot operation applied to the pivoted Data Set does not recreate exactly the original Data Set (before pivoting).

Examples

Given the Data Set DS_1:

Input DS_1 (see structure)

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

Example 1

DS_r := DS_1 [ unpivot Id_2, Me_1 ];

results in (see structure):

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