vwsergewv / dynamic_pivot_dbt Goto Github PK

{% macro dynamic_pivot (columns_to_show, pivot_column, aggregate_column, my_table) %}


{%- set my_query -%}
SELECT distinct {{pivot_column}}  FROM {{ ref(my_table) }} ;
{%- endset -%}

{%- set results = run_query(my_query) -%} 

{%- if execute -%}
{#- Return the first column -#}
{%- set items = results.columns[0].values() -%}
{%- else -%}
{%- set items = [] -%}
{%- endif %}



SELECT {{columns_to_show}} ,
{%- for i in items %}
sum("{{i}}_views") AS "{{i}}_views"
{%- if not loop.last %} , {% endif -%}
{%- endfor %}
FROM (
        SELECT  {{columns_to_show}},  
        {%- for i in items %}
        CASE WHEN {{pivot_column}} = '{{i}}'  THEN {{aggregate_column}} ELSE 0 END AS "{{i}}_views"
        {%- if not loop.last %} , {% endif -%}
        {%- endfor %}
        FROM {{ ref(my_table) }}   )
GROUP BY  {{columns_to_show}} 
{% endmacro %}