dbt - pivoting on list of values

Say you have a massive table but for ease of use for reporting you need to split numbers out. For example say you are a service that provides numerous different services, each person gets a single form with a mark for each service provided and the transactional system stores this a a row per form per service. For reporting you may well want to pivot these out and have a separate columns for each service with a 1 / 0 and a count for each. 

In traditional databases and tools you have to create all your views in advanced and you cannot pivot on a list of values. The joy I am finding with dbt and jinja coding is that you can create the views dynamically. And whilst that may pose some risk if means that in this scenario a new service code is catered for automatically as you can dynamically generate the column by looping through a list of values. 

I have applied this logic my strength exercise data, pivoting it from rows to columns. Below is the jinja code and the resulting SQL, 

The other thing I used here was from the utils was slugify utility to replace spaces with underscores in my strings so I can use them in column names.

	{%- set activities = dbt_utils.get_column_values(
	table=ref('stg_exercise_star'),
	column= 'description'
	) -%}
	SELECT
	{{ dbt_utils.current_timestamp() }} last_refresh,
	date_done,
	{% for activity in activities -%}
	sum(case when description = '{{ activity }}' then act_count else 0 end)
	as {{ dbt_utils.slugify(activity) }}_count
	{%- if not loop.last -%}
	,
	{% endif -%}
	{%- endfor %}
	from {{ ref('stg_exercise_star') }}
	group by 2

view raw loopvaluesExercisePivot.sql hosted with ❤ by GitHub

	SELECT
	current_timestamp::
	timestamp_ntz
	last_refresh,
	date_done,
	sum(case when description = 'Pull Up' then act_count else 0 end)
	as pull_up_count,
	sum(case when description = 'Crunch' then act_count else 0 end)
	as crunch_count,
	sum(case when description = 'Push Up' then act_count else 0 end)
	as push_up_count,
	sum(case when description = 'Dumbbells' then act_count else 0 end)
	as dumbbells_count,
	sum(case when description = 'Box Jump' then act_count else 0 end)
	as box_jump_count,
	sum(case when description = 'Plank Time' then act_count else 0 end)
	as plank_time_count
	from KEBOOLA_7127.WORKSPACE_15661914.stg_exercise_star
	group by 2

view raw pivoted_gen.sql hosted with ❤ by GitHub