With dynamic pivots in Insights, you can create custom tables tailored to your data needs and visualize data in various formats. The dynamic pivot function is used in the query editor and enables users on non-GXP tenants to group lists into tables by rearranging rows into columns. This article covers:

Note: Insights is only available to tenants on Enterprise or Industry plans.

What are dynamic pivots?

Dynamic pivots are based on dynamic data sources, with ranges that adjust automatically when data is added or removed. Pivots take slices of that data and spread it out into distinct columns, pivoting how the data is displayed.

If you have a list of items in rows, you can turn that list into a table where one of the attributes is now the column, allowing you to reorganize content in ways that make it easier to find the information you need.

For example, the following table displays data we queried about notebook entries:

We can use the dynamic pivot function to pivot this data by created_month. Now, it displays the data as a table where user_name defines rows and created_month defines columns.

Test it out by adding this query to your query editor:

select dynamic_pivot(1,

'select u.name user_name, date_trunc(''month'', e.created_at)::date created_month, count(e.id) countof from entry$raw e left join user$raw u on e.creator_id = u.id group by 1,2',

'select distinct date_trunc(''month'', e.created_at)::date from entry$raw e'

)

Use dynamic pivots

The base function dynamic_pivot(ARGUMENT) can use several arguments:

number_of_row_fields

The number of columns in your data_query’s output that you want to keep as rows. The unique values of these columns remain as distinct rows in the pivoted output table, as opposed to being turned into columns in the pivoted table. For example, if a data_query has four columns and number_of_row_fields has two, then the first and second column are preserved as rows.

data_query

A text argument representing the main query of the underlying data that you want to pivot. The data_query should have number_of_row_fields + two columns that it returns:

  • The first number_of_row_fields columns are the row-based fields you’re keeping as rows

  • The second to last column is the field you’re making the distinct columns in your pivoted table

  • The last column is the values you want in the body of your pivot table

column_query

A text argument representing the query that defines the columns of your pivoted table. The values in the second to last column of your data_query are matched against these.

In the example below, we perform a dynamic pivot to make created_month a column field instead of a row field and keep user_name or entry_template_id as distinct rows.

Below is the logic used to complete the dynamic pivot function:

  • number_of_row_fields is 2, since we want user_name and entry_template_id to both remain as rows.

  • data_query is the query that generated the table above:

    ‘select u.name user_name, entry_template_id, date_trunc(''month'', e.created_at)::date created_month, count(e.id) countof from aweb.entry$raw e left join aweb.user$raw u on e.creator_id = u.id group by 1,2,3'

    Note the following to make this query work:

    • The query must be used in the function as a string, so it must have single quotes at the start and end of the query.

    • To make single quotes for strings inside the query work, escape them. In Postgres, you need to use another single quote. For example, if the underlying query has a function like date_trunc('month', e.created_at), change this to date_trunc(''month'', e.created_at) so that the string isn’t ended prematurely.

    • The columns need to match the spec of what the data query is expected to return. Here, since number_of_row_fields is 2, there should be four columns:

      • Col 1: A row field (user_name)

      • Col 2: A row field (entry_template_id)

      • Col 3: The field that will become columns (created_month)

      • Col 4: The data in the body of the pivoted table (countof)

  • column_query is the query that produces the distinct column headers you want. Here, this is created_month values, so the query is: 'select distinct date_trunc(''month'', e.created_at)::date from aweb.entry$raw e where e.entry_template_id is not null'

Using this logic, the dynamic pivot function is:

select dynamic_pivot(

2,

'select u.name user_name,

entry_template_id,

date_trunc(''month'', e.created_at)::date created_month,

count(e.id) countof

from aweb.entry$raw e

left join aweb.user$raw u on e.creator_id = u.id

group by 1,2,3',

'select distinct date_trunc(''month'', e.created_at)::date

from aweb.entry$raw e'

)

Note: At this time, you can only designate a single column field, so column_query must always return a single column.

Did this answer your question?