dynamic_pivot_multiple_variables.sql

===
Compiled SQL:
select
    EXTRACT(YEAR from ship_date),
    sum(case when ship_mode = 'FOB' then gross_item_sales_amount end) as FOB_A_amount,
    sum(case when ship_mode = 'TRUCK' then gross_item_sales_amount end) as TRUCK_N_amount,
    sum(case when ship_mode = 'MAIL' then gross_item_sales_amount end) as MAIL_N_amount,
    sum(case when ship_mode = 'SHIP' then gross_item_sales_amount end) as SHIP_R_amount,
    sum(case when ship_mode = 'AIR' then gross_item_sales_amount end) as AIR_R_amount,
    sum(case when ship_mode = 'RAIL' then gross_item_sales_amount end) as RAIL_A_amount,
    sum(case when ship_mode = 'RAIL' then gross_item_sales_amount end) as RAIL_R_amount,
    sum(case when ship_mode = 'SHIP' then gross_item_sales_amount end) as SHIP_N_amount,
    sum(case when ship_mode = 'MAIL' then gross_item_sales_amount end) as MAIL_A_amount,
    sum(case when ship_mode = 'TRUCK' then gross_item_sales_amount end) as TRUCK_R_amount,
    sum(case when ship_mode = 'SHIP' then gross_item_sales_amount end) as SHIP_A_amount,
    sum(case when ship_mode = 'RAIL' then gross_item_sales_amount end) as RAIL_N_amount,
    sum(case when ship_mode = 'AIR' then gross_item_sales_amount end) as AIR_N_amount,
    sum(case when ship_mode = 'FOB' then gross_item_sales_amount end) as FOB_N_amount,
    sum(case when ship_mode = 'FOB' then gross_item_sales_amount end) as FOB_R_amount,
    sum(case when ship_mode = 'MAIL' then gross_item_sales_amount end) as MAIL_R_amount,
    sum(case when ship_mode = 'TRUCK' then gross_item_sales_amount end) as TRUCK_A_amount,
    sum(case when ship_mode = 'AIR' then gross_item_sales_amount end) as AIR_A_amount
    

from `sales-demo-project-314714`.`dbt_eongaro`.`fct_order_items`
group by 1
limit 500
===


/* Create a pivot table with dynamic columns based on the ship modes that are in the system */

{%- call statement('result', fetch_result=True) -%}

    {# get ship modes and return flags from the fct_order_items table - stores them as a python list of tuples [('N', 'FOB'), ('A', 'TRUCK') #}
    select return_flag,ship_mode from {{ ref('fct_order_items') }} 
    where ship_mode != 'REG AIR'
    group by 1,2 
    

{%- endcall %}

{% set ship_modes = load_result('result').data %}

select
    EXTRACT(YEAR from ship_date),
    {# Loop over ship_modes list and access the tuple items #}
    {%- for ship_mode in ship_modes -%}
        sum(case when ship_mode = '{{ship_mode[1]}}' then gross_item_sales_amount end) as {{ship_mode[1]|replace(' ', '_')}}_{{ship_mode[0]}}_amount
        {%- if not loop.last -%},{% endif %}
    {% endfor %}

from {{ ref('fct_order_items') }}
group by 1