Type | Read/write | Author | Availability |
|---|---|---|---|
Read | Finbourne | Provided with LUSID |
The Tools.Unpivot provider enables you to write a Luminesce query that rotates data from columns into rows, in the same way as the SQL Server and Snowflake UNPIVOT functions.
You can use this provider in conjunction with other providers to unpivot data; see example 3.
See also: Tools.Pivot
Basic usage
@input = select * from <some-provider> where <filter-expression>;
@unpivoted =
use Tools.Unpivot with @input
--<optional-arguments>
enduse;
select * from @unpivoted; Input tables
Tools.Unpivot takes in one input table and outputs a table of data; see example 1.
Options
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
--noHeader) sets it to True; omitting the option specifies Falsetakes multiple string values, then specify a comma-separated list, for example
--names=My,Column,Names
Current options at article update time are listed in the table below. For the very latest information, run the following query using a suitable tool and examine the online help:
@x = use Tools.Unpivot
--help
enduse;
select * from @xCurrent options | Explanation |
|---|---|
| Mandatory. The column names (comma delimited) that should be preserved in rows (other columns will become values) normally these would make up the unique key of the input data. [String] |
| The column names specified in --key normally make up the unique key of the input data. Specifying this flag asserts this to not be the case (the returned data can be harder to interpret). [Boolean] |
| Null cells should still produce rows. [Boolean] |
Examples
Example 1: Unpivoting basic input data
In this example, we input some simple data...
...before unpivoting that data using column1 and column2 as our key:
@input = values
('a', 'a', 'AA', 1, #2022-02-01#, true, 1.1),
('a', 'b', 'BB', 2, #2022-02-02#, false, null),
('b', 'a', 'CC', 3, #2022-02-03#, true, 1.3),
('b', 'b', 'DD', 4, #2022-02-04#, false, 1.4);
@unpivoted =
use Tools.Unpivot with @input
--key=column1, column2
enduse;
select * from @unpivoted The table of data returned after unpivoting shows column1 and 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 column1 and 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:
@input = values
('a', 'a', 'AA', 1, #2022-02-01#, true, 1.1),
('a', 'a', 'BB', 2, #2022-02-02#, false, 1.11)
('b', 'b', 'DD', 4, #2022-02-04#, false, 1.4);
@unpivoted =
use Tools.Unpivot with @input
--key=column1, column2
--keyIsNotUnique
enduse;
select * from @unpivoted The table of data returned by the query looks like this:
Example 3: Pivoting a reconciliation response to show metrics in separate columns
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, TimeZone:
@input = select * from Lusid.CutLabel;
@unpivoted =
use Tools.Unpivot with @input
--key=TimeZone
--keyIsNotUnique
enduse;
select * from @unpivoted 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:
