dbt-utils/macros/sql/pivot.sql at main · tconbeer/dbt-utils · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
{#
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
    distinct: Whether to use distinct in the aggregation, default is False
#}
{% macro pivot(
    column,
    values,
    alias=True,
    agg="sum",
    cmp="=",
    prefix="",
    suffix="",
    then_value=1,
    else_value=0,
    quote_identifiers=True,
    distinct=False
) %}
    {{
        return(
            adapter.dispatch("pivot", "dbt_utils")(
                column,
                values,
                alias,
                agg,
                cmp,
                prefix,
                suffix,
                then_value,
                else_value,
                quote_identifiers,
                distinct,
            )
        )
    }}
{% endmacro %}

{% macro default__pivot(
    column,
    values,
    alias=True,
    agg="sum",
    cmp="=",
    prefix="",
    suffix="",
    then_value=1,
    else_value=0,
    quote_identifiers=True,
    distinct=False
) %}
    {% for value in values %}
        {{ agg }} (
            {% if distinct %}distinct {% endif %}
            case
                when
                    {{ column }} {{ cmp }} '{{ dbt_utils.escape_single_quotes(value) }}'
                then {{ then_value }}
                else {{ else_value }}
            end
        )
        {% if alias %}
            {% if quote_identifiers %} as {{ adapter.quote(prefix ~ value ~ suffix) }}
            {% else %} as {{ dbt_utils.slugify(prefix ~ value ~ suffix) }}
            {% endif %}
        {% endif %}
        {% if not loop.last %},{% endif %}
    {% endfor %}
{% endmacro %}