Direct providerReadFinbourneProvided with LUSID

The Tools.Pivot provider enables you to write a Luminesce query that pivots data from rows into columns to accomplish the same types of transformations as the SQL Server and Snowflake PIVOT functions. 

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 pivot data, see example 3.

See also: Tools.Unpivot

Basic usage

@data = select * from <some-provider> where <filter-expression>;

@pivoted =
use Tools.Pivot with @data

select * from @pivoted

Input tables

Tools.Pivot takes in one input table and outputs a table of data, see example 1.


Tools.Pivot has a number of optional arguments which can be specified to customise how the provided data is pivoted:

--key<column_name>OptionalThe column name that should have its values pivoted into columns. These values must be unique. Defaults to the first non-numeric column.
--aggregateColumns<column_name>,<column_name>OptionalExplicit list of columns to treat as aggregates. Defaults to all numeric columns.
--columnNameFormatStringOptionalHow to format key and aggregate columns together if there is more than one aggregate column. Defaults to {key}_{argument}.
--matchStringCaseBooleanOptionalDefaults to match ignoring case. Set this to include case differences.
--matchDoubleToExponentInt32OptionalDouble values are considered the 'same' if they differ only after x decimal places. Defaults to 8.


Example 1: Pivoting basic input data

In this example, we input and aggregate some simple data before pivoting that data. 

@input = select 'a' as x, 1 as y union all select 'b', 2;
@pivoted = 
use Tools.Pivot with @input
select * from @pivoted

The table of data returned before versus after pivoting looks like this:

Example 2: 

In this example, we create an input table which looks like this...

...and pivot the data using the Name column as the key and Num and NumMax as aggregates to produce a column for each Name value.

@input = 
select 'A' as Name, 1 as Num, 10 as NumMax
union all 
select 'B-b' as Name, 2 as Num, 20 as NumMax
union all 
select 'C.c' as Name, 3 as Num, 30 as NumMax;
@pivoted = 
use Tools.Pivot with @input
--columnNameFormat={key} {aggregate}


select * from @pivoted

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.Portfolio.Reconciliation.Generic response that looks like this...

...and run it through the Tools.Pivot provider to return one row per holding instead of one per metric, with each metric shown in duplicated columns rather than in the same columns in different rows.

@lookup_table = select
    'FBNUniversity' as LeftPortfolioScope,
    'T01004' as LeftPortfolioCode,
    #2022-03-07 16:29:00# as LeftValuationDate,
    'FBNUniversity' as RightPortfolioScope,
    'T01004' as RightPortfolioCode,
    #2022-03-07 16:30:00# as RightValuationDate

@metrics = values
    ('Holding/default/Units', 'Sum', null),
    ('Instrument/default/Name', 'Value', null)
@formatted_metrics = select
    column1 as 'MeasureName',
    column2 as 'Operation',
    column3 as 'ReconciliationSide'
from @metrics

@response = select ^ from Lusid.Portfolio.Reconciliation.Generic
    where ToLookUp = @lookup_table
    and MeasuresToReturn = @formatted_metrics

@pivot = use Tools.Pivot with @response

select * from @pivot

The table of data returned by the query looks like this, with one row per holding instead of two, and the Instrument/default/Name and Holding/default/Units metrics shown in separate columns: