|Direct provider||Read||Finbourne||Provided with LUSID|
Tools.Unpivot provider enables you to write a Luminesce query that rotates data from columns into rows to accomplish the same types of transformations as the SQL Server and Snowflake
Note: The LUSID user running the query must have sufficient access control permissions to use this provider. This should automatically be the case if you are the domain owner.
You can use this provider in conjunction with other providers to unpivot data, see example 3.
See also: Tools.Pivot
Tools.Unpivot takes in one input table and outputs a table of data, see example 1.
Tools.Unpivot has options that enable you to refine a query.
An option takes the form
--<option>=<value>, for example
--key=LusidInstrumentId. Note no spaces are allowed either side of the
= operator. If an option takes a boolean value, then specifying that option (for example
--keyIsNotUnique) sets it to True; omitting the option specifies False.
The table below provides information on available options for
|Mandatory||The column names that should be preserved in rows (other columns will become values). Normally these would make up the unique key of the input data.|
|Boolean||Optional||Column names specified in |
|Boolean||Optional||Specifies whether null cells should still produce rows.|
To see a help screen of available options, their data types, default values, and an explanation for each, run the following query using a suitable tool:
@x = use Tools.Unpivot --help enduse; select * from @x
In this example, we input some simple data...
...before unpivoting that data using
column2 as our key.
The table of data returned after unpivoting shows
column2 preserved as rows, while the other columns have become values:
Example 2: Unpivoting data with a non-unique key
In this example, we create an input table which looks like this...
...and unpivot the data using
column2 as our key. Notice that the key (highlighted in red) is not unique in this example, so we pass in the argument
--keyIsNotUnique to prevent the query from failing.
The table of data returned by the query looks like this:
You can take a Lusid.CutLabel response that looks like this...
...and run it through the
Tools.Unpivot provider to return rows for the key column,
The table of data returned by the query looks like this, with the
TimeZone column preserved as rows while the other columns have become values: