Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
3 contributors

Users who have contributed to this file

@mplovepop @carlyfk @peterfine
70 lines (61 sloc) 1.79 KB
{#
Pivot values from rows to columns.
Example:
Input: `public.test`
| size | color |
|------+-------|
| S | red |
| S | blue |
| S | red |
| M | red |
select
size,
{{ dbt_utils.pivot('color', dbt_utils.get_column_values('public.test',
'color')) }}
from public.test
group by size
Output:
| size | red | blue |
|------+-----+------|
| S | 2 | 1 |
| M | 1 | 0 |
Arguments:
column: Column name, required
values: List of row values to turn into columns, required
alias: Whether to create column aliases, default is True
agg: SQL aggregation function, default is sum
cmp: SQL value comparison, default is =
prefix: Column alias prefix, default is blank
suffix: Column alias postfix, default is blank
then_value: Value to use if comparison succeeds, default is 1
else_value: Value to use if comparison fails, default is 0
quote_identifiers: Whether to surround column aliases with double quotes, default is true
#}
{% macro pivot(column,
values,
alias=True,
agg='sum',
cmp='=',
prefix='',
suffix='',
then_value=1,
else_value=0,
quote_identifiers=True) %}
{% for v in values %}
{{ agg }}(
case
when {{ column }} {{ cmp }} '{{ v }}'
then {{ then_value }}
else {{ else_value }}
end
)
{% if alias %}
{% if quote_identifiers %}
as {{ adapter.quote(prefix ~ v ~ suffix) }}
{% else %}
as {{prefix ~ v ~ suffix }}
{% endif %}
{% endif %}
{% if not loop.last %},{% endif %}
{% endfor %}
{% endmacro %}
You can’t perform that action at this time.