Pivoting a column with an apostrophe in DBT

Question

I am trying to pivot a column with an apostrophe but this is very difficult in DBT. Any ideas? I tried double quotes but dbt does not pick this up, and I cannot use a like statement in a pivot.

 {% set pvt_details=[
      ('General liability cover_rated_premium' , 'gl_premium')
    , ('Contractors' errors and omissions cover_rated_premium','eo_premium') ] %}
 WITH filtered AS (
     SELECT
     quote_id
    , target
    , premium_after_amount
 from {{ source('acdc', 'chopin_quote_rating_steps') }} cqrs
 WHERE target IN ({% for column in pvt_details %} '{{column[0]}}' {%- if not loop.last -%} 
, {%- endif %}
  {% endfor %})
    AND action = 'initial_premium'
  )
  select *
  from filtered
  pivot(sum(premium_after_amount)
   for target in ({% for column in pvt_details %} '{{column[0]}}' {%- if not loop.last -%} , 
{%- endif %}
{% endfor %}))
  as p (quote_id,
        {% for column in pvt_details %} {{column[1]}} {%- if not loop.last -%} , {%- endif 
%}
        {% endfor %})

You have this tagged as SQL - can you specify additional info like the distro + version? i.e. Postgres 11.8, SQL Server 2017 etc. — sgdata, May 31 '20 at 01:53
If this is Postgres, you might find the following helpful: https://stackoverflow.com/a/9170637/3073340, "Double your single quotes to escape them". Just a thought - don't know enough about the situation to provide more insight. — sgdata, May 31 '20 at 02:01

score 1 · Answer 1 · edited Feb 17 '21 at 00:11

Try this link in DBT macros. There is a macro just for pivots.

https://github.com/fishtown-analytics/dbt-utils/blob/master/macros/sql/pivot.sql

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    |

dbt_utils.pivot()

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 %}

Would you be so kind as to post a single answer, unless you have fundamentally different approaches to offer, as, generally [multiple answers by the same user are not acceptable](https://meta.stackoverflow.com/questions/251070/are-multiple-answers-by-the-same-user-acceptable). Please also ensure that it is [well formatted](https://stackoverflow.com/editing-help), particularly to ensure that code is readable. — David Buck, Jul 15 '20 at 14:31

score 1 · Answer 2 · edited Sep 21 '20 at 22:21

{% set pvt_details=[
('General liability cover_rated_premium', 'gl_premium'),
("Contractor\\\'s errors and omissions cover_rated_premium", 'eo_premium') 
] %}

select
  concat_ws(' :: ',
    {% for column in pvt_details %} 
        '{{ column[0] }}'
    {%- if not loop.last -%},  {%- endif %}
    {% endfor %}
) as column_selection
from {{ ref('reference_model') }}
limit 1

Collapse

From Christine at DBT. Jinja2 seems to have an issue with quoting in the way that a lot of other languages handle it =/.

score 0 · Answer 3 · answered Jun 25 '20 at 12:46

if they want to stick their own query, I suspect swapping out

'Contractors' errors and omissions cover_rated_premium'

with "Contractors' errors and omissions cover_rated_premium" where you use "

instead of ' might fix it? but not 100% sure if anything else is causing issues here. I do think the pivot macro of utils should work great! Just not sure if it’ll handle the ' well (I think it should). They also won’t be able to rename the column in the same step as the pivot (which is what I think is happening here) but they could easily use the pivot function, then rename the columns in a succeeding CTE

From Andrew at DBT

Pivoting a column with an apostrophe in DBT

3 Answers3