DBT Utils PIVOT, with MAX as aggregation and else NULL

Archive
1

I’m trying to leverage the PIVOT utility to break various text fields into their respective columns. My current code reads…

select 
    metafield_owner_id as product_id,
    {{ dbt_utils.pivot(
        'metafield_key',
        dbt_utils.get_column_values( ref('stg_shopify_product_metafield'), 'metafield_key'),
    ) }}
    from {{ ref('stg_shopify_product_metafield') }}
group by metafield_owner_id 
order by 1,2

As written above, I’m getting a good pivot with sum as the aggregation, and else 0.
I’d like the aggregation to be “max,” which will populate each field with the text value, and the ELSE to be null.

I’ve been able to make this work with the compiled SQL in dbeaver, but struggling to write the code properly in dbt itself.

Any help would be greatly appreciated.

2

within the dbt_utils.pivot you need to specify agg=max and else_value=null.

3

Thanks for the reply, Derek. I tried that, but perhaps didn’t have the syntax right initially.

I was able to rewrite and got back the results I was expecting.

Thank you!