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

# Building a dynamic pivot query view

> Build a query view hat cross-joins sparse key-value data so every entity has a row for every attribute, then exposes it as a pivotable query view.

export const categoryIcons = {
  'administration': 'lock',
  'api': 'terminal',
  'connections': 'database',
  'dashboards': 'table-columns',
  'embed': 'code',
  'errors': 'exclamation',
  'modeling': 'wrench',
  'patterns': 'plus',
  'schedules & alerts': 'envelope',
  'visualizations': 'chart-column',
  'workbooks': 'book'
};

export const GuideSidebar = ({category, relatedLinks, updatedDate}) => {
  const [progress, setProgress] = React.useState(0);
  React.useEffect(() => {
    const sidebar = document.querySelector('.guide-sidebar');
    if (!sidebar) return;
    let container = sidebar.parentElement;
    while (container && !container.querySelector('.guide-header')) {
      container = container.parentElement;
    }
    if (container && !container.classList.contains('guide-page-layout')) {
      container.classList.add('guide-page-layout');
    }
  }, []);
  React.useEffect(() => {
    const handleScroll = () => {
      const scrollTop = window.scrollY;
      const docHeight = document.documentElement.scrollHeight - window.innerHeight;
      const scrollPercent = docHeight > 0 ? scrollTop / docHeight * 100 : 0;
      setProgress(Math.min(100, Math.max(0, scrollPercent)));
    };
    window.addEventListener('scroll', handleScroll, {
      passive: true
    });
    handleScroll();
    return () => window.removeEventListener('scroll', handleScroll);
  }, []);
  const icon = category ? categoryIcons[category.toLowerCase()] || 'book' : 'book';
  return <aside className="guide-sidebar">
      <div className="guide-sidebar-content">
        <a href="/guides" className="guide-sidebar-back">
          <Icon icon="arrow-left" iconType="solid" size={14} />
          <span>All guides</span>
        </a>

        <div className="guide-sidebar-section">
          <div className="guide-sidebar-label">Progress</div>
          <div className="guide-sidebar-progress">
            <div className="guide-mascot">
              <svg viewBox="0 0 688 690" width="48" height="48">
                <defs>
                  <clipPath id="progressClip">
                    <rect x="0" y={0} width="688" height={progress * 6.9} />
                  </clipPath>
                </defs>

                {}
                <path d="M343.67 1.5C542.684 1.5 685.84 149.351 685.84 344.84C685.84 540.328 542.685 688.18 343.67 688.18C144.655 688.18 1.5 540.318 1.5 344.84C1.50007 149.361 144.655 1.50005 343.67 1.5Z" fill="#FCFCF7" stroke="#FF5FA2" strokeWidth="3" />

                {}
                <path d="M343.67 0C143.81 0 0 148.55 0 344.84C0 541.13 143.81 689.68 343.67 689.68C543.53 689.68 687.34 541.14 687.34 344.84C687.34 148.54 543.53 0 343.67 0Z" fill="#FF5FA2" clipPath="url(#progressClip)" />

                {}
                <path d="M337.89 319.29C337.89 336.75 322.49 350.14 302.81 349.83C286.18 349.57 273.89 337.29 274.37 321.45C274.88 304.82 290.91 290.88 309.98 290.44C325.69 290.09 337.88 302.69 337.88 319.29H337.89Z" fill="#4D122C" />
                <path d="M566.17 319.29C566.17 336.75 550.77 350.14 531.09 349.83C514.46 349.57 502.17 337.29 502.65 321.45C503.16 304.82 519.19 290.88 538.26 290.44C553.97 290.09 566.16 302.69 566.16 319.29H566.17Z" fill="#4D122C" />
                <path d="M367.74 342.07C360.22 346.32 359.4 354.9 370.62 366.4C381.85 377.9 399.76 389.56 420.81 389.18C441.88 389.1 460.67 377.72 472.47 363.53C473.83 361.93 478.84 356.88 478.51 351.07C478.32 348.35 476.17 341.19 467.83 341.38C463.46 341.44 461.21 343.68 456.69 347.36C445.2 356.14 432.7 361.21 420.56 361.27C408.43 361.43 395.68 356.17 385.39 347.22C380.32 342.81 375.25 337.82 367.74 342.07Z" fill="#4D122C" />
              </svg>
            </div>
            <span className="guide-sidebar-progress-text">{Math.round(progress)}%</span>
          </div>
        </div>

        {category && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Category</div>
            <div className="guide-sidebar-category">
              <Icon icon={icon} iconType="solid" size={14} />
              <span>{category}</span>
            </div>
          </div>}

        {updatedDate && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Last updated</div>
            <div className="guide-sidebar-date">{updatedDate}</div>
          </div>}

        {relatedLinks && relatedLinks.length > 0 && <div className="guide-sidebar-section">
            <div className="guide-sidebar-label">Related</div>
            <ul className="guide-sidebar-links">
              {relatedLinks.map((link, index) => <li key={index}>
                  <a href={link.href}>{link.title}</a>
                </li>)}
            </ul>
          </div>}
      </div>
    </aside>;
};

export const GuideTitle = ({title}) => {
  return <div className="guide-header">
      <h1 className="guide-title">{title}</h1>
    </div>;
};

<GuideSidebar
  categoryIcons={categoryIcons}
  category="patterns"
  updatedDate="May 2026"
  relatedLinks={[
{ title: "Query views", href: "/modeling/query-views" },
{ title: "Templated filters", href: "/modeling/templated-filters" },
{ title: "Promoting model changes", href: "/modeling/develop/promotion" }
]}
/>

<GuideTitle title="Building a dynamic pivot query view" />

This guide walks through building a query view in Omni that takes sparse key-value data and fills in the gaps so the result can be cleanly pivoted in a workbook.

It covers setting up the SQL using a cross join, wiring up a scoped templated filter, and defining the YAML dimensions and measures that make pivoting work.

This is especially relevant if:

* Your source table has rows shaped like `entity_id | attribute_name | attribute_value`, and not every entity has a row for every attribute name
* You want to pivot attribute names into columns in an Omni workbook without missing rows collapsing silently
* You need a dynamic filter that scopes the query to a subset of data (for example, by a parent ID) and populates its dropdown from the database

## Understanding the pattern

If your data looks like this:

| entity\_id | attribute\_name | attribute\_value |
| ---------- | --------------- | ---------------- |
| abc        | Evidence A      | Pass             |
| abc        | Evidence C      | Fail             |
| xyz        | Evidence B      | Pass             |

A direct pivot would silently drop the missing combinations. The cross-join pattern fills them in first, so the result looks like this before pivoting:

| entity\_id | Evidence A | Evidence B | Evidence C |
| ---------- | ---------- | ---------- | ---------- |
| abc        | Pass       | NULL       | Fail       |
| xyz        | NULL       | Pass       | NULL       |

The query view does this by cross-joining all distinct entity IDs against all distinct attribute names, then left-joining back to the source table to pick up values where they exist.

<Note>
  The [`fill_fields`](/modeling/query-views/parameters/fill-fields) query view parameter solves a related but different problem. It fills missing values from an enumerated or date series. The cross-join approach here is for cases where both the entities and attributes are dynamic and sourced from the data itself.
</Note>

## Requirements

To follow the steps in this guide, you'll need:

* **Modeler** or **Connection Admin** permissions on the model
* The fully qualified path to your table (`DATABASE.SCHEMA.TABLE_NAME`)
* A table with sparse key-value rows

  <Note>
    This guide uses Snowflake to walk through examples. If using a different database, you may need to adjust the SQL to your database's syntax.
  </Note>

## Building the query view

<Steps>
  <Step title="Confirm the table path" titleSize="h3" id="confirm-table-path">
    Before you start working in Omni, confirm the fully qualified path to your table in Snowflake. If you're unsure, run the following in Snowflake:

    ```sql wrap theme={null}
    SHOW TABLES LIKE '%YOUR_TABLE_NAME%' IN ACCOUNT;
    ```

    Then confirm the exact database and schema:

    ```sql wrap theme={null}
    SELECT table_catalog, table_schema, table_name
    FROM information_schema.tables
    WHERE table_name = 'YOUR_TABLE_NAME';
    ```

    Your full table path will be: `DATABASE.SCHEMA.TABLE_NAME`
  </Step>

  <Step title="Create the query view file" titleSize="h3" id="create-query-view-file">
    Query views can be created two ways in Omni: through the model IDE (recommended), or from a workbook.

    <Tabs>
      <Tab title="In the IDE (recommended)" icon="code">
        1. Open the Omni model IDE.
        2. Create a file named `your_query_view.query.view` (the `.query.view` extension is required).
        3. Paste the YAML from the [next section](#add-yaml) into the file.
        4. Click **Save**.
      </Tab>

      <Tab title="In the workbook" icon="book">
        1. Build an initial query in a workbook.
        2. Click **Model > Save query as view**.
        3. Enter the name for your view (for example, `your_query_view`).
        4. Click **Create query view**. A new View tab will open.
        5. Replace the auto-generated YAML with the template from the next step, then click **Save changes to view**.
      </Tab>
    </Tabs>
  </Step>

  <Step title="Add the YAML template" titleSize="h3" id="add-yaml">
    Paste the following template into your query view file. Every value in `ALL_CAPS` is a placeholder you'll replace with a real value in the next step — except `CREATED_AT`, which is an optional column you can rename or remove (see the inline comment in the template).

    <Note>
      The placeholder table in the next step covers all substitutions. For example, if your scoping column is `ACCOUNT_ID`, replace every instance of `SCOPE_COLUMN` with `ACCOUNT_ID` and rename `scope_id_dim` to `account_id_dim` everywhere it appears — including in `suggest_from_field`.
    </Note>

    ```yaml title="your_query_view.query.view" expandable wrap theme={null}
    # Replace "your_query_view" everywhere — including inside the templated filter references in the SQL.
    # Example: if your file is my_pivot_view.query.view, use my_pivot_view here and in the SQL.
    sql: |-
      WITH all_attribute_names AS (
        SELECT DISTINCT "ATTRIBUTE_NAME_COLUMN"
        FROM DATABASE.SCHEMA.YOUR_TABLE
        WHERE {{# your_query_view.scope_filter.filter }} "SCOPE_COLUMN" {{/ your_query_view.scope_filter.filter }}
      ),
      all_entity_ids AS (
        SELECT DISTINCT "ENTITY_ID_COLUMN"
        FROM DATABASE.SCHEMA.YOUR_TABLE
        WHERE {{# your_query_view.scope_filter.filter }} "SCOPE_COLUMN" {{/ your_query_view.scope_filter.filter }}
      ),
      all_combinations AS (
        SELECT
          c."ENTITY_ID_COLUMN",
          a."ATTRIBUTE_NAME_COLUMN"
        FROM all_entity_ids c
        CROSS JOIN all_attribute_names a
      ),
      filled AS (
        SELECT
          ac."ENTITY_ID_COLUMN",
          ac."ATTRIBUTE_NAME_COLUMN",
          t."ATTRIBUTE_VALUE_COLUMN",
          t."CREATED_AT",
          -- Selects the scope column from the source row. NULL for cross-join rows with no matching source data.
          t."SCOPE_COLUMN" AS "SCOPE_COLUMN"
        FROM all_combinations ac
        LEFT JOIN DATABASE.SCHEMA.YOUR_TABLE t
          ON t."ENTITY_ID_COLUMN" = ac."ENTITY_ID_COLUMN"
          AND t."ATTRIBUTE_NAME_COLUMN" = ac."ATTRIBUTE_NAME_COLUMN"
          AND {{# your_query_view.scope_filter.filter }} t."SCOPE_COLUMN" {{/ your_query_view.scope_filter.filter }}
      )
      SELECT * FROM filled
      ORDER BY "ENTITY_ID_COLUMN", "ATTRIBUTE_NAME_COLUMN"

    dimensions:
      entity_id:
        sql: '"ENTITY_ID_COLUMN"'
        label: "Entity ID"

      # Right-click this field in the Omni workbook and select "Pivot"
      attribute_name:
        sql: '"ATTRIBUTE_NAME_COLUMN"'
        label: "Attribute Name"

      # The _dim suffix avoids a naming collision with the measure defined below
      attribute_value_dim:
        sql: '"ATTRIBUTE_VALUE_COLUMN"'
        label: "Attribute Value"

      # The _dim suffix avoids a naming collision with the scope_filter defined below.
      # This dimension exists so scope_filter can use suggest_from_field to populate
      # its dropdown dynamically. If you rename this, update suggest_from_field below to match.
      scope_id_dim:
        sql: '"SCOPE_COLUMN"'
        label: "Scope ID"

      # Remove this block (and t."CREATED_AT" from the SQL above) if you don't need a date filter.
      created_at:
        sql: '"CREATED_AT"'
        label: "Created At"

    measures:
      count:
        aggregate_type: count

      attribute_value:
        sql: ${attribute_value_dim}
        aggregate_type: max
        label: "Attribute Value (Aggregated)"

    filters:
      scope_filter:
        type: string
        default_filter:
          is: your-default-value-here
        suggest_from_field: your_query_view.scope_id_dim
    ```

    <Note>
      Setting a default filter value is important. Without one, the cross-join CTEs will scan the entire source table on first load.
    </Note>
  </Step>

  <Step title="Replace placeholders in YAML" titleSize="h3" id="replace-placeholders">
    Replace each placeholder in the YAML using the table below:

    | Placeholder                                              | Replace with                                                                             | Example                                |
    | -------------------------------------------------------- | ---------------------------------------------------------------------------------------- | -------------------------------------- |
    | `your_query_view`                                        | The name of the query view file (without `.query.view`)                                  | `my_pivot_view`                        |
    | `DATABASE.SCHEMA.YOUR_TABLE`                             | The fully qualified Snowflake table path                                                 | `ANALYTICS.PROD.FACT_EVENTS`           |
    | `ENTITY_ID_COLUMN`                                       | The column that identifies each unique entity (the rows in your pivot)                   | `PRODUCT_ID`                           |
    | `ATTRIBUTE_NAME_COLUMN`                                  | The column whose distinct values become the pivot column headers                         | `METRIC_NAME`                          |
    | `ATTRIBUTE_VALUE_COLUMN`                                 | The column containing the values to show in each pivot cell                              | `METRIC_VALUE`                         |
    | `SCOPE_COLUMN`                                           | The column used to filter the data down to a relevant subset                             | `ACCOUNT_ID`                           |
    | `CREATED_AT`                                             | **Optional**. The timestamp column to expose as a date dimension - remove if not needed. | `EVENT_CREATED_AT`                     |
    | `scope_id_dim`                                           | Rename to match your scope column, keeping the `_dim` suffix                             | `account_id_dim`                       |
    | `your_query_view.scope_id_dim` (in `suggest_from_field`) | Update to match your renamed `scope_id_dim` value                                        | `my_pivot_view.account_id_dim`         |
    | `your-default-value-here`                                | The default value to pre-populate the scope filter                                       | `a1b2c3d4-e5f6-7890-abcd-ef1234567890` |

    Once all placeholders are replaced, your query view is ready to test.
  </Step>

  <Step title="Enable pivoting in the workbook" titleSize="h3" id="enable-pivoting">
    Omni requires at least one measure on a query to enable pivoting, which is why this view defines `attribute_value` as a `max` measure.

    <Note>
      This pattern assumes at most one value per entity + attribute (+ scope) combination. If your source has multiple rows for the same combination, the `max` measure returns only the highest value lexically and drops the others. De-duplicate upstream or change the aggregation if that isn't what you want.
    </Note>

    Once the query view is saved and the model is valid:

    1. Open a workbook and select your query view as the data source.
    2. Add **Entity ID** as a dimension.
    3. Add **Attribute Value (Aggregated)** as a measure.
    4. Right-click **Attribute Name** in the field browser and select **Pivot**.

    Each unique attribute name will become its own column, with `NULL` shown where no value existed.

    To change the scope (for example, to view a different parent ID), apply the **Scope Filter** filter-only field that appears in the field browser under this view's filters.
  </Step>

  <Step title="Promote to the shared model" titleSize="h3" id="promote-to-shared-model">
    Once you've confirmed the query view works as expected in the workbook, promote it to make it available across all workbooks.

    In a published workbook, click **Model > View & promote changes**, review the changes, and click **Promote to shared**.
  </Step>
</Steps>

## Next steps

* [Query view parameters](/modeling/query-views/parameters) — Full reference for query view syntax and options
* [Templated filters](/modeling/templated-filters) — How filter templating works in Omni SQL
* [Promoting model changes](/modeling/develop/promotion) — How to move changes from a draft to the shared model