Tools.Pivot

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

You can use this provider in conjunction with other providers to pivot LUSID data: see example 3.

See also: Tools.Unpivot

Basic usage

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

@pivoted =
use Tools.Pivot with @data
--<optional-arguments>
enduse;

select * from @pivoted

Input tables

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

Options

Tools.Pivot 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 False

• takes 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.Pivot
--help
enduse;
select * from @x

Examples

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
enduse;
select * from @pivoted

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

Example 2: Aggregating and pivoting data using particular columns

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
--key=Name
--aggregateColumns=Num,NumMax
--columnNameFormat={key} {aggregate}
enduse;

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
--key=Measure
--aggregateColumns=LeftMeasureValue,RightMeasureValue,Difference,ResultComparison
enduse
;
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: