---
title: "Tools.Pivot"
slug: "toolspivot"
updated: 2026-02-07T10:50:32Z
published: 2026-02-07T10:50:32Z
canonical: "support.lusid.com/toolspivot"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Tools.Pivot

| **Type** | **Read/write** | **Author** | **Availability** |
| --- | --- | --- | --- |
| [Direct provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider) | Read | Finbourne | Provided with LUSID |

The `Tools.Pivot` provider enables you to write a [Luminesce query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that pivots data from rows into columns, in the same way as the [SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16) and [Snowflake](https://docs.snowflake.com/en/sql-reference/constructs/pivot.html) `PIVOT` functions.

You can use this provider in conjunction with other providers to pivot LUSID data: [see example 3](/v1/docs/toolspivot#example-3-pivoting-a-reconciliation-response-to-show-metrics-in-separate-columns).

**See also:** [Tools.Unpivot](/v1/docs/toolsunpivot)

## Basic usage

```sql
@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](/v1/docs/toolspivot#example-1-pivoting-basic-input-data).

## Options

`Tools.Pivot` has options that enable you to refine a query.

An option takes the form `--&lt;option&gt;=&lt;value&gt;`, 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](/v1/docs/what-tools-are-available-to-write-luminesce-queries) and examine the online help:

```sql
@x = use Tools.Pivot
--help
enduse;
select * from @x
```

| Current options | Explanation |
| --- | --- |
| `key` | The column name that should have its values be pivoted into columns. If not specified defaults to the first non-numeric column. [String] |
| `aggregateColumns` | Explicit list of columns to treat as aggregates. If not specified defaults to all numeric columns. [String] |
| `columnNameFormat` | How to format Key and Aggregate names together (if there is more than one aggregate column). This should contain {key} and {aggregate} which will be replaced by the key value and aggregate source column name. [String, Default: {key}_{aggregate}] |
| `matchStringCase` | Normally strings are considered a match ignoring case. Set this to include case differences. [Boolean] |
| `matchDoubleToExponent` | Double values are considered the 'same' if they differ only after X decimal places. [Int32, Default: 8] |

## Examples

### Example 1: Pivoting basic input data

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

```sql
@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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/b83eaa0c-92cd-4b45-ab8a-1c39496375a1.png)

### Example 2: Aggregating and pivoting data using particular columns

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

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/69e65e2f-f682-4304-84d2-bea0602d5c37.png)

...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:

```sql
@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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/4cac0fae-d5c1-40a5-b6c7-51ec84dd38d2.png)

### 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...

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/126650a5-6326-4e62-bf85-210cd40c6e69.png)

...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:

```sql
@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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/7a6007a2-ef91-438f-951b-7e7fd54cbd80.png)