# US 2025/0094418 A1 · Dynamic pivot implementation using object aggregation

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2025/0094418 A1
- Document ID: US20250094418A1
- Status: Published application
- Kind: A1
- Application number: 18/525,811
- Filed: 2023-11-30
- Published: 2025-03-20
- Earliest claimed: 2023-09-18
- Assignees: Snowflake Inc.
- Applicants: Snowflake Inc.
- Inventors: Srilakshmi Chintala; Jianzhun Du; Naresh Kumar; Jaeyun Noh; Srinath Shankar; Leonhard Franz Spiegelberg; Eric Shawn Vandenberg; Jiaqi Yan; Andong Zhan; Yun Zou
- Web page: https://patentgrep.com/patent/US20250094418A1

## CPC

- G06F 16/24534: {Query rewriting; Transformation}
- G06F 16/24542: {Plan optimisation}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

Examples provide a method for performing dynamic pivot queries employing PANDA-compatible semantics inside a cloud data platform workload. A SQL engine receives a pivot query specifying a pivot column for dynamically determining pivot values in a dynamic pivot operation. The SQL engine identifies, in the pivot query, a keyword or a subquery indicating dynamic determination of the pivot values. Based on identifying the keyword or the subquery indicating the dynamic determination of the pivot values, the SQL engine determines distinct pivot values for the pivot column by evaluating the keyword or executing the subquery. The evaluating of the keyword includes issuing a query to a data store to obtain the distinct pivot values for the pivot column. The SQL engine performs the dynamic pivot operation using the distinct pivot values for the pivot column.

## Claims

### Claim 1

A method comprising:
receiving a pivot query specifying a pivot column for dynamically determining a pivot value in a dynamic pivot operation, the dynamic pivot operation comprising specifying a distinct pivot value dynamically at runtime;
identifying, in the pivot query, a subquery indicating dynamic determination of the pivot value;
based on identifying the subquery indicating the dynamic determination of the pivot value, specifying the distinct pivot value for the pivot column at the runtime by executing the subquery, the executing of the subquery comprising transmitting a query to a data store to obtain the distinct pivot value for the pivot column; and
performing, by at least one hardware processor, the dynamic pivot operation using the distinct pivot value for the pivot column.

### Claim 2 (depends on 1)

The method of claim 1, wherein performing of the dynamic pivot operation further comprises:
sorting output pivot columns based on an order specified in the subquery.

### Claim 3 (depends on 1)

The method of claim 1, wherein the subquery specifies an order for the pivot value.

### Claim 4 (depends on 1)

The method of claim 1, further comprising:
rewriting the pivot query to produce a rewritten pivot query, wherein the rewriting utilizes object aggregation to aggregate data for the pivot column; and
executing the rewritten pivot query utilizing the object aggregation to perform the dynamic pivot operation.

### Claim 5 (depends on 4)

The method of claim 4, wherein rewriting the pivot query further comprises:
aggregating the data for the pivot column into an object containing a key and a value;
extracting the pivot value from the object to generate extracted pivot value; and
populating output columns based on the extracted pivot value.

### Claim 6 (depends on 5)

The method of claim 5, wherein utilizing the object aggregation avoids individually aggregating the data for each potential pivot value.

### Claim 7 (depends on 1)

The method of claim 1, wherein receiving the pivot query specifying the pivot column further comprises:
receiving the pivot query to pivot a dataset based on values in the pivot column.

### Claim 8 (depends on 1)

The method of claim 1, wherein receiving the pivot query further comprises:
receiving a second pivot query including at least one of input data, an aggregation metric, or a second pivot column;
parsing the second pivot query into an abstract syntax tree representation including a pivot operator node; and
applying optimization rules to rewrite the pivot operator node using object aggregation and deterministic ordering techniques according to an optimized logical plan.

### Claim 9 (depends on 8)

The method of claim 8, further comprising:
converting the optimized logical plan into a physical distributed execution plan with shuffle stages for intermediate data exchange; and
dispatching the physical distributed execution plan to worker nodes for parallel distributed execution across data partitions.

### Claim 10 (depends on 9)

The method of claim 9, further comprising:
sending intermediate shuffle data between the worker nodes via a cluster resource manager;
sorting final pivot results; and
providing the final pivot results to a client application.

### Claim 11

A system comprising:
one or more hardware processors of a machine; and
at least one memory storing instructions that, when executed by the one or more hardware processors, cause the system to perform operations comprising:
receiving a pivot query specifying a pivot column for dynamically determining a pivot value in a dynamic pivot operation, the dynamic pivot operation comprising specifying a distinct pivot value dynamically at runtime;
identifying, in the pivot query, a subquery indicating dynamic determination of the pivot value;
based on identifying the subquery indicating the dynamic determination of the pivot value, specifying the distinct pivot value for the pivot column at the runtime by executing the subquery, the executing of the subquery comprising transmitting a query to a data store to obtain the distinct pivot value for the pivot column; and
performing the dynamic pivot operation using the distinct pivot value for the pivot column.

### Claim 12 (depends on 11)

The system of claim 11, wherein performing of the dynamic pivot operation further comprises:
sorting output pivot columns based on an order specified in the subquery.

### Claim 13 (depends on 11)

The system of claim 11, wherein the subquery specifies an order for the pivot value.

### Claim 14 (depends on 11)

The system of claim 11, the operations comprising:
rewriting the pivot query to produce a rewritten pivot query, wherein the rewriting utilizes object aggregation to aggregate data for the pivot column; and
executing the rewritten pivot query utilizing the object aggregation to perform the dynamic pivot operation.

### Claim 15 (depends on 14)

The system of claim 14, wherein rewriting the pivot query further comprises:
aggregating the data for the pivot column into an object containing a key and a value;
extracting the pivot value from the object to generate extracted pivot value; and
populating output columns based on the extracted pivot value.

### Claim 16 (depends on 15)

The system of claim 15, wherein utilizing the object aggregation avoids individually aggregating the data for each potential pivot value.

### Claim 17 (depends on 11)

The system of claim 11, wherein receiving the pivot query specifying the pivot column further comprises:
receiving the pivot query to pivot a dataset based on values in the pivot column.

### Claim 18 (depends on 11)

The system of claim 11, wherein receiving the pivot query further comprises:
receiving a second pivot query including at least one of input data, an aggregation metric, or a second pivot column;
parsing the second pivot query into an abstract syntax tree representation including a pivot operator node; and
applying optimization rules to rewrite the pivot operator node using object aggregation and deterministic ordering techniques according to an optimized logical plan.

### Claim 19 (depends on 18)

The system of claim 18, the operations comprising:
converting the optimized logical plan into a physical distributed execution plan with shuffle stages for intermediate data exchange; and
dispatching the physical distributed execution plan to worker nodes for parallel distributed execution across data partitions.

### Claim 20 (depends on 19)

The system of claim 19, the operations comprising:
sending intermediate shuffle data between the worker nodes via a cluster resource manager;
sorting final pivot results; and
providing the final pivot results to a client application.

### Claim 21

A machine-storage medium embodying instructions that, when executed by a machine, cause the machine to perform operations comprising:
receiving a pivot query specifying a pivot column for dynamically determining a pivot value in a dynamic pivot operation, the dynamic pivot operation comprising specifying a distinct pivot value dynamically at runtime;
identifying, in the pivot query, a subquery indicating dynamic determination of the pivot value;
based on identifying the subquery indicating the dynamic determination of the pivot value, specifying the distinct pivot value for the pivot column at the runtime by executing the subquery, the executing of the subquery comprising transmitting a query to a data store to obtain the distinct pivot value for the pivot column; and
performing the dynamic pivot operation using the distinct pivot value for the pivot column.

### Claim 22 (depends on 21)

The machine-storage medium of claim 21, wherein performing of the dynamic pivot operation further comprises:
sorting output pivot columns based on an order specified in the subquery.

### Claim 23 (depends on 21)

The machine-storage medium of claim 21, wherein the subquery specifies an order for the pivot value.

### Claim 24 (depends on 21)

The machine-storage medium of claim 21, wherein the operations comprise:
rewriting the pivot query to produce a rewritten pivot query, wherein the rewriting utilizes object aggregation to aggregate data for the pivot column; and
executing the rewritten pivot query utilizing the object aggregation to perform the dynamic pivot operation.

### Claim 25 (depends on 24)

The machine-storage medium of claim 24, wherein rewriting the pivot query further comprises:
aggregating the data for the pivot column into an object containing a key and a value;
extracting the pivot value from the object to generate extracted pivot value; and
populating output columns based on the extracted pivot value.

### Claim 26 (depends on 25)

The machine-storage medium of claim 25, wherein utilizing the object aggregation avoids individually aggregating the data for each potential pivot value.

### Claim 27 (depends on 21)

The machine-storage medium of claim 21, wherein receiving the pivot query specifying the pivot column further comprises:
receiving the pivot query to pivot a dataset based on values in the pivot column.

### Claim 28 (depends on 21)

The machine-storage medium of claim 21, wherein receiving the pivot query further comprises:
receiving a second pivot query including at least one of input data, an aggregation metric, or a second pivot column;
parsing the second pivot query into an abstract syntax tree representation including a pivot operator node; and
applying optimization rules to rewrite the pivot operator node using object aggregation and deterministic ordering techniques according to an optimized logical plan.

### Claim 29 (depends on 28)

The machine-storage medium of claim 28, wherein the operations comprise:
converting the optimized logical plan into a physical distributed execution plan with shuffle stages for intermediate data exchange; and
dispatching the physical distributed execution plan to worker nodes for parallel distributed execution across data partitions.

### Claim 30 (depends on 29)

The machine-storage medium of claim 29, wherein the operations comprise:
sending intermediate shuffle data between the worker nodes via a cluster resource manager;
sorting final pivot results; and
providing the final pivot results to a client application.