I have a list variable created like this: {% set options = ["a", "b", "c"] %}
I want to use it in a SQL CTE like this:
df as (
select *
from my_table
pivot (sum(value) for option in ({{options}}))
)
When the SQL is compiled by DBT, the result is:
df as (
select *
from my_table
pivot (sum(value) for option in (["a", "b", "c"]))
)
And that won't work because of the brackets. So how can I use the list variable without including the brackets?
@larsks is right that you want to create a string from your list, not change the way the list is displayed.
The easiest way to do this is to use the join filter in jinja:
{% set options = ["a", "b", "c"] %}
{{ options | join(", ")}}
-- compiles to:
a, b, c
That works fine for numbers, but to get string literals into your sql query, you'll need to quote the values in your list. I prefer to do this by adding nested quotes in the list itself:
{% set options = ["'a'", "'b'", "'c'"] %}
{{ options | join(", ")}}
-- compiles to:
'a', 'b', 'c'
But you can also put the extra quotes inside the argument to join, and concatenate an extra quote to the beginning and end of your string:
{% set options = ["a", "b", "c"] %}
{{ "'" ~ options | join("', '") ~ "'"}}
-- compiles to:
'a', 'b', 'c'
Or you can wrap you jinja expression in a single quote to achieve the same thing, but I think this is hard to read::
{% set options = ["a", "b", "c"] %}
'{{ options | join("', '") }}'
-- compiles to:
'a', 'b', 'c'