> ## Documentation Index
> Fetch the complete documentation index at: https://lightdash-06-24-docs-simplify-date-zoom-usage-description.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Run SQL pivot query

> Run a SQL pivot query



## OpenAPI

````yaml https://raw.githubusercontent.com/lightdash/lightdash/refs/heads/main/packages/backend/src/generated/swagger.json post /api/v1/projects/{projectUuid}/sqlRunner/runPivotQuery
openapi: 3.0.0
info:
  title: Lightdash API
  version: 0.3228.0
  description: >
    Open API documentation for all public Lightdash API endpoints. #
    Authentication Before you get started, you might need to create a Personal
    Access Token to authenticate via the API. You can create a token by
    following this guide: https://docs.lightdash.com/references/personal_tokens
  license:
    name: MIT
  contact:
    name: Lightdash Support
    email: support@lightdash.com
    url: https://docs.lightdash.com/help-and-contact/contact/contact_info/
servers:
  - url: /
security: []
tags:
  - name: My Account
    description: These routes allow users to manage their own user account.
  - name: Organizations
    description: >-
      Each user is a member of a single organization. These routes allow users
      to manage their organization. Most actions are only available to admin
      users.
  - name: Projects
    description: >-
      Projects belong to a single organization. These routes allow users to
      manage their projects, browse content, and execute queries. Users inside
      an organization might have access to a project from an organization-level
      role or they might be granted access to a project directly.
  - name: Spaces
    description: >-
      Spaces allow you to organize charts and dashboards within a project. They
      also allow granular access to content by allowing you to create private
      spaces, which are only accessible to the creator and admins.
  - name: Roles & Permissions
    description: >-
      These routes allow users to manage roles and permissions for their
      organization.
    externalDocs:
      url: https://docs.lightdash.com/references/roles
  - name: Query
    description: >-
      These routes allow users to execute and manage queries against their data
      warehouse. This includes metric queries, SQL queries, and retrieving query
      results.
paths:
  /api/v1/projects/{projectUuid}/sqlRunner/runPivotQuery:
    post:
      tags:
        - SQL runner
      summary: Run SQL pivot query
      description: Run a SQL pivot query
      operationId: runSqlPivotQuery
      parameters:
        - in: path
          name: projectUuid
          required: true
          schema:
            type: string
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/SqlRunnerPivotQueryBody'
      responses:
        '200':
          description: Success
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ApiJobScheduledResponse'
        default:
          description: Error
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ApiErrorPayload'
      deprecated: true
      security: []
components:
  schemas:
    SqlRunnerPivotQueryBody:
      allOf:
        - $ref: '#/components/schemas/SqlRunnerBody'
        - $ref: '#/components/schemas/ApiSqlRunnerPivotQueryPayload'
    ApiJobScheduledResponse:
      properties:
        results:
          properties:
            jobId:
              type: string
          required:
            - jobId
          type: object
        status:
          type: string
          enum:
            - ok
          nullable: false
      required:
        - results
        - status
      type: object
    ApiErrorPayload:
      properties:
        error:
          properties:
            data:
              $ref: '#/components/schemas/AnyType'
              description: Optional data containing details of the error
            message:
              type: string
              description: A friendly message summarising the error
            name:
              type: string
              description: Unique name for the type of error
            statusCode:
              type: number
              format: integer
              description: HTTP status code
          required:
            - name
            - statusCode
          type: object
        status:
          type: string
          enum:
            - error
          nullable: false
      required:
        - error
        - status
      type: object
      description: |-
        The Error object is returned from the api any time there is an error.
        The message contains
    SqlRunnerBody:
      properties:
        limit:
          type: number
          format: double
        sql:
          type: string
      required:
        - sql
      type: object
    ApiSqlRunnerPivotQueryPayload:
      allOf:
        - $ref: '#/components/schemas/PivotConfiguration'
        - properties:
            savedSqlUuid:
              type: string
          type: object
    AnyType:
      description: |-
        This AnyType is an alias for any
        The goal is to make it easier to identify any type in the codebase
        without having to eslint-disable all the time
        These are only used on legacy `any` types, don't use it for new types.
        This is added on a separate file to avoid circular dependencies.
    PivotConfiguration:
      properties:
        pivotColumnsOrder:
          items:
            $ref: '#/components/schemas/GroupByColumn'
          type: array
          description: >-
            Declared order of the pivot-column dimensions (visible
            `groupByColumns`

            plus hidden `sortOnlyDimensions`) as they appear in the chart's

            `pivotConfig.columns`. `column_ranking` orders columns by this
            sequence so

            a hidden sort-only dim sorts at its DECLARED position — hiding a dim
            then

            leaves column order identical to when it was visible, instead of
            hoisting

            the hidden dim to the front of the ORDER BY. Passthrough (hidden,
            non-sort)

            dims are excluded since they don't drive sort. When omitted,
            ordering falls

            back to hoisting sort-only sort targets to the front (legacy
            behavior).
        passthroughDimensions:
          items:
            $ref: '#/components/schemas/GroupByColumn'
          type: array
          description: >-
            Hidden pivot-column dimensions that are NOT sort targets but still
            need

            their values carried through the SQL pipeline so that other fields'

            `richText` / `image:` templates can reference them via

            `row.<table>.<field>.raw`. They participate in `group_by_query`
            SELECT

            and GROUP BY (same as sortOnlyDimensions) but do NOT affect

            `column_ranking` ORDER BY (they don't drive sort). Without this
            bucket

            the dim would be dropped entirely from the query and `row.*.raw`

            references would silently resolve to undefined.
        sortOnlyDimensions:
          items:
            $ref: '#/components/schemas/GroupByColumn'
          type: array
          description: >-
            Dimensions referenced by ORDER BY but NOT spread into pivot columns.

            Used when a user hides a dim that's part of `pivotConfig.columns`
            and has

            a sort entry on it: the dim still ranks column order via the GROUP
            BY /

            ORDER BY pipeline, but it doesn't become a pivot column header
            level.

            Mirrors `sortOnlyColumns` (which serves the same purpose for
            metrics).
        sortOnlyColumns:
          items:
            $ref: '#/components/schemas/ValuesColumn'
          type: array
          description: >-
            Metrics/table calculations needed for sort anchor CTEs but not for
            display.

            These are merged into valuesColumns for SQL generation in
            PivotQueryBuilder,

            but excluded from pivotDetails so they don't appear as chart series.
        metricsAsRows:
          type: boolean
          description: |-
            When true, metrics are displayed as rows instead of columns.
            This affects column limit calculation - when metrics are rows,
            we don't need to divide the column limit by the number of metrics.
            Defaults to false for backward compatibility (SQL runner behavior).
        sortBy:
          $ref: '#/components/schemas/SortBy'
        groupByColumns:
          items:
            $ref: '#/components/schemas/GroupByColumn'
          type: array
        valuesColumns:
          items:
            $ref: '#/components/schemas/ValuesColumn'
          type: array
        indexColumn:
          anyOf:
            - $ref: '#/components/schemas/PivotIndexColum'
            - items:
                $ref: '#/components/schemas/PivotIndexColum'
              type: array
      required:
        - valuesColumns
      type: object
    GroupByColumn:
      properties:
        reference:
          type: string
      required:
        - reference
      type: object
    ValuesColumn:
      properties:
        aggregation:
          $ref: '#/components/schemas/VizAggregationOptions'
        reference:
          type: string
      required:
        - aggregation
        - reference
      type: object
    SortBy:
      items:
        $ref: '#/components/schemas/VizSortBy'
      type: array
    PivotIndexColum:
      properties:
        type:
          $ref: '#/components/schemas/VizIndexType'
        reference:
          type: string
      required:
        - type
        - reference
      type: object
    VizAggregationOptions:
      enum:
        - sum
        - count
        - avg
        - min
        - max
        - any
      type: string
    VizSortBy:
      properties:
        pivotValues:
          items:
            $ref: '#/components/schemas/PivotSortAnchor'
          type: array
          description: Pins the row-sort anchor to a specific pivot column.
        nullsFirst:
          type: boolean
        direction:
          $ref: '#/components/schemas/SortByDirection'
        reference:
          type: string
      required:
        - direction
        - reference
      type: object
    VizIndexType:
      enum:
        - time
        - category
      type: string
    PivotSortAnchor:
      properties:
        value:
          anyOf:
            - type: string
            - type: number
              format: double
            - type: boolean
          nullable: true
        reference:
          type: string
      required:
        - value
        - reference
      type: object
      description: |-
        Coordinates of a single pivot column, used to anchor a row sort to that
        specific column when results are pivoted.
    SortByDirection:
      enum:
        - ASC
        - DESC
      type: string

````