Define list of strings from query results to use in macro

I am trying to define a list of strings to use in a macro but I want the list of strings to come from the results of a query. Is this possible?

For example the results from this query would be a list of veggie names

select name from veggies

I want to use the results of the query to define a list like below

{% set veggies = ['carrots', 'potato', 'broccoli', 'corn'] %}

I would then use the macro to loop through the list. Is this possible?

see this relevant doc:

Note: @Andrew originally posted this reply in Slack. It might not have transferred perfectly.

1 Like

U can use run_query to run a query on ur warehouse and access results.

{% set query = ’ SELECT NAME FROM TABLE_NAME’ %}
{% set results = run_query(query) %}

{% if execute %}
{% set values = results.columns[0].values %}
{{ print(values) }}
{% endif %}

You can also use dbt_utils.get_column_values which will handle a lot of the setup that using run_query directly otherwise requires

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.