# Casting column datatypes based on a seed

**URL:** <https://discourse.getdbt.com/t/casting-column-datatypes-based-on-a-seed/4877>\
**Category:** Show and Tell\
**Tags:** jinja, seeds\
**Created:** [December 17, 2020, 2:33pm UTC](https://discourse.getdbt.com/t/casting-column-datatypes-based-on-a-seed/4877 "2020-12-17T14:33:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![leogodin](https://avatars.discourse-cdn.com/v4/letter/l/7feea3/32.png) [@leogodin](https://discourse.getdbt.com/u/leogodin)\
**Post date:** [December 17, 2020, 2:33pm UTC](https://discourse.getdbt.com/t/casting-column-datatypes-based-on-a-seed/4877/1 "2020-12-17T14:33:45Z")

</div>

Old thread, I know, but I ran into this recently. We have a simple data dictionary that lists source column name, target column name and data type. At the same time, we are pivoting key/value pairs to a wide format.

To do that, I created a macro that queries the column metadata then uses cases statements to pivot, rename and set the data type (source data is read in as all varchars). It works well.

Here’s the macro (recordset identifies source data, record\_pip identifies the target):

```
{% macro get_field_definitions(record_set, record_pipe) %}
    {% set query %}
       select
           field_key,
           field_type,
           target_name
       from {{ ref('stg_field_definitions') }}
       where record_set = '{{record_set}}'
       and record_pipe = '{{record_pipe}}'
    {% endset %} 

    {% set query_results = run_query(query) %}

    {% if execute %}
        {% set results = query_results.rows %}
        {{ return(results) }}
    {% endif %}
{% endmacro %}

```

In SQL:

```
{{
    config(
        materialized='view'
    )
}}

{#- Get the field definitions that exist -#}
{% set field_definitions = get_field_definitions(
                                                             record_set = 'SomeRecordset',
                                                             record_pipe = 'SomeTargetEntity')
 %}

select
    identifier,
        {% for row in field_definitions %}
            cast( max(case when field_key = {{ row[0] }} then field_value end) as {{row[1]}} ) as "{{ row[2] }}",
        {% endfor %}
    source_file,
    sum(is_changed) as num_changed
from {{ ref('stg_some_long_key_value_table') }}
group by
    dentifier,
    source_file -- We have daily files coming in with changes

```

A few notes:

- Tried dbt\_utils.pivot, but it doesn’t support using a second column as the value (that I could find)
- This is my first attempt at creating a macro, I’d love any feedback
- I was surprised how easy this was. It really sold me on DBT as a great solution to dynamic SQL

---

<div class="post-metadata">

**Author:** ![anl2455](https://avatars.discourse-cdn.com/v4/letter/a/7ba0ec/32.png) [@anl2455](https://discourse.getdbt.com/u/anl2455)\
**Post date:** [April 11, 2024, 7:01pm UTC](https://discourse.getdbt.com/t/casting-column-datatypes-based-on-a-seed/4877/2 "2024-04-11T19:01:58Z")

</div>

Interested if you could expand on this. I am trying to do the same, if I understand correctly:

I have columns record\_id, field\_names, and field\_values and am trying to pivot and make the values of the field\_names columns with the associcated field\_values as the data populating the pivot.