CODEWITHSUNDEEP
jinja2dbt
Nick
Nick

Reputation: 3281

How to pivot in DBT without aggregating the value

Using DBT, how does one pivot a table without aggregating? When looking at the pivot macro it mentions the aggregation function, which defaults to sum.

To visualize the problem:

Color Date Amount
red 2023-01-01 1
blue 2023-01-01 2
red 2023-01-02 3
blue 2023-01-02 7

Into

Date red blue
2023-01-01 1 2
2023-01-02 3 7

So instead of aggregating, it would move the value

Upvotes: 1

Views: 212

Answers (1)

Aleix CC
Aleix CC

Reputation: 2099

In your use case, even if you perform some aggregation, the values would remain the same since you only have one occurrence per day and color.

So, I believe the syntax would look like this:

select
  date,
  {{ dbt_utils.pivot(
      'color',
      dbt_utils.get_column_values(ref('your_model_name'), 'color'),
      then_value='amount'
  ) }}
from {{ ref('your_model_name') }}
group by 1

Upvotes: 2

Related Questions