---
title: "Tools.Unpivot"
slug: "toolsunpivot"
tags: ["luminesce ", "provider"]
updated: 2026-02-07T10:56:00Z
published: 2026-02-07T10:56:00Z
canonical: "support.lusid.com/toolsunpivot"
---

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

| **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.Unpivot` provider enables you to write a [Luminesce query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that rotates data from columns into rows, 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/unpivot.html#unpivot) `UNPIVOT` functions.

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

**See also:** [Tools.Pivot](/v1/docs/toolspivot)

## Basic usage

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

## Options

`Tools.Unpivot` 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.Unpivot
--help
enduse;
select * from @x
```

| Current options | Explanation |
| --- | --- |
| `key` | **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] |
| `keyIsNotUnique` | 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] |
| `includeNulls` | Null cells should still produce rows. [Boolean] |

## Examples

### Example 1: Unpivoting basic input data

In this example, we input some simple data...

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/22549098-c905-49d2-be2f-a1b9d00b1913.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A13%3A51Z&se=2026-11-04T03%3A13%3A51Z&sr=c&sp=r&sig=jgpoD4jV0%2BHiKXWq6I%2BOSb9gO2q9vNtMyrhlTZVwHBk%3D)

...before unpivoting that data using `column1` and `column2` as our key:

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

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/3e0f2c49-71f5-4d45-895a-523be769d0a1.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A13%3A51Z&se=2026-11-04T03%3A13%3A51Z&sr=c&sp=r&sig=jgpoD4jV0%2BHiKXWq6I%2BOSb9gO2q9vNtMyrhlTZVwHBk%3D)

### Example 2: Unpivoting data with a non-unique key

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

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/d0804ea2-8294-4748-9742-f5dfcdb432ca.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A13%3A51Z&se=2026-11-04T03%3A13%3A51Z&sr=c&sp=r&sig=jgpoD4jV0%2BHiKXWq6I%2BOSb9gO2q9vNtMyrhlTZVwHBk%3D)

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

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

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/722ed678-ab55-4e53-83d7-e301e1613f69.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A13%3A51Z&se=2026-11-04T03%3A13%3A51Z&sr=c&sp=r&sig=jgpoD4jV0%2BHiKXWq6I%2BOSb9gO2q9vNtMyrhlTZVwHBk%3D)

### Example 3: Pivoting a reconciliation response to show metrics in separate columns

You can take a [Lusid.CutLabel](/v1/docs/lusidcutlabel) response that looks like this...

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/499e12fa-17ff-4569-95dd-440d7f5a0865.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A13%3A51Z&se=2026-11-04T03%3A13%3A51Z&sr=c&sp=r&sig=jgpoD4jV0%2BHiKXWq6I%2BOSb9gO2q9vNtMyrhlTZVwHBk%3D)

...and run it through the `Tools.Unpivot` provider to return rows for the key column, `TimeZone`:

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

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/4ca60dbd-0a39-4e21-a765-7056f98d9104.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A13%3A51Z&se=2026-11-04T03%3A13%3A51Z&sr=c&sp=r&sig=jgpoD4jV0%2BHiKXWq6I%2BOSb9gO2q9vNtMyrhlTZVwHBk%3D)