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	variant	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