Unpivot Table

Rotate the column values into rows values.

For unpivot details and examples, see Unpivot. For limitations, see Unpivot Limitations.

Unpivot is used to normalize tables that are built for cross tabular reporting purposes. The unpivot operator rotates the column values for all the pivoted columns. A variable column, value column and all columns from the source table except the unpivot columns are projected into the result table. The variable column and value columns in the result table indicate the pivoted column name and values respectively.

The response is returned as a dynamic schema. For details see: dynamic schemas documentation.

Input Parameter Description

Name	Type	Description
table_name	string	Name of the table on which the operation will be performed. Must be an existing table/view.
column_names	array of strings	List of column names or expressions. A wildcard '*' can be used to include all the non-pivoted columns from the source table.
variable_column_name	string	Specifies the variable/parameter column name. The default value is ''.
value_column_name	string	Specifies the value column name. The default value is ''.
pivoted_columns	array of strings	List of one or more values typically the column names of the input table. All the columns in the source table must have the same data type.
encoding	string	Specifies the encoding for returned records. Supported Values Description binary Indicates that the returned records should be binary encoded. json Indicates that the returned records should be json encoded.
options	map of string to strings	Optional parameters. The default value is an empty map ( {} ). Supported Parameters (keys) Parameter Description collection_name Name of a collection which is to contain the table specified in result_table. If the collection provided is non-existent, the collection will be automatically created. If empty, then the table will be a top-level table. result_table The name of the table used to store the results. Has the same naming restrictions as tables. If present, no results are returned in the response. result_table_persist If true, then the result table specified in result_table will be persisted and will not expire unless a ttl is specified. If false, then the result table will be an in-memory table and will expire unless a ttl is specified otherwise. The supported values are: true false expression Filter expression to apply to the table prior to unpivot processing. order_by Comma-separated list of the columns to be sorted by; e.g. 'timestamp asc, x desc'. The columns specified must be present in input table. If any alias is given for any column name, the alias must be used, rather than the original column name. The default value is ''. chunk_size Indicates the chunk size to be used for the result table. Must be used in combination with the result_table option. limit The number of records to keep. The default value is ''. ttl Sets the TTL of the table specified in result_table. view_id view this result table is part of. The default value is ''. materialize_on_gpu If true then the output columns will be cached on the GPU. The supported values are: true false create_indexes Comma-separated list of columns on which to create indexes on the table specified in result_table. The columns specified must be present in output column names. If any alias is given for any column name, the alias must be used, rather than the original column name. result_table_force_replicated Force the result table to be replicated (ignores any sharding). Must be used in combination with the result_table option. The supported values are: true false

Output Parameter Description

Name	Type	Description
table_name	string	Typically shows the result-table name if provided in the request (Ignore otherwise).
response_schema_str	string	Avro schema of output parameter binary_encoded_response or output parameter json_encoded_response.
binary_encoded_response	bytes	Avro binary encoded response.
json_encoded_response	string	Avro JSON encoded response.
total_number_of_records	long	Total/Filtered number of records.
has_more_records	boolean	Too many records. Returned a partial set.