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.
Comments
Post a Comment