unpivot

Type of operation: Aggregate

Description

The unpivot verb rotates a table by transforming a list of columns into rows.

This verb can be used to turn a wide table (e.g. timestamp, percent_load, percent_mem, percent_disk) into a narrower table (e.g. timestamp, op, percent). unpivot is not the exact reverse of pivot as it cannot undo the aggregations done in pivot.

The arguments are as follows:

  • name_column: Specifies the name of the name column which stores the name of the column a value comes from. Must be a constant string. Optional. Default value is name.

  • value_column: Specifies the name of the value column which stores values from each of the name_value columns passed in. Must be a constant string. Optional. Default value is value.

  • name_value_0, ..., name_value_n: List of columns to generate rows from.

Additional details:

  • The name_column and value_column arguments, if specified, need to be specified together

  • The name and value columns will be inserted where the first name_value column used to be

  • unpivot is not supported for resource datasets

  • unpivot will drop the primary key and any candidate keys

  • group_by and order_by specifications are not allowed

Usage

unpivot [ name_column ], [ value_column ], name_value_1, name_value_2, ...

Argument

Type

Optional

Repeatable

Restrictions

name_column

string

yes

no

constant

value_column

string

yes

no

constant

name_value

storable

no

yes

column

Accelerable

unpivot is always accelerable if the input is accelerable. A dataset that only uses accelerable verbs can be accelerated, making queries on the dataset respond faster.

Examples

unpivot percent_load, percent_mem, percent_disk

Consider the following input table A:

timestamp

percent_load

percent_mem

percent_disk

10/31/2023 13:51:27

3.57

41.72

54.71

10/31/2023 13:52:43

39.04

40.24

20.72

The above statement transforms table A to:

timestamp

name

value

10/31/2023 13:51:27

“percent_load”

3.57

10/31/2023 13:51:27

“percent_mem”

41.72

10/31/2023 13:51:27

“percent_disk”

54.71

10/31/2023 13:52:43

“percent_load”

39.04

10/31/2023 13:52:43

“percent_mem”

40.24

10/31/2023 13:52:43

“percent_disk”

20.71

unpivot "op", "percent", percent_load, percent_mem, percent_disk

The above statement transforms table A to:

timestamp

op

percent

10/31/2023 13:51:27

“percent_load”

3.57

10/31/2023 13:51:27

“percent_mem”

41.72

10/31/2023 13:51:27

“percent_disk”

54.71

10/31/2023 13:52:43

“percent_load”

39.04

10/31/2023 13:52:43

“percent_mem”

40.24

10/31/2023 13:52:43

“percent_disk”

20.71