如何在dbt中实现多列透视，生成带_date后缀的日期列？

原始数据表（my_model.sql） #

期望的最终结果 #

当前已实现的代码（仅透视AGE列） #

{% set pivot_cols = dbt_utils.get_column_values(table = ref('my_table'), column = 'names')%}

with pivot as (
select * from {{ ref('my_table')}} 
pivot(  max(AGE) for names in ( 
    {% for pivot_col in pivot_cols %}
    '{{pivot_col}}'{% if not loop.last %}, {% endif %}
    {% endfor %}))

select * from pivot

当前代码运行结果 #

解决方案：同时透视AGE和DATE列 #

修改pivot子句，同时指定两个字段的聚合逻辑，再通过列重命名匹配目标格式，代码如下：

{% set pivot_cols = dbt_utils.get_column_values(table = ref('my_table'), column = 'names')%}

with pivot as (
select * from {{ ref('my_table')}} 
pivot(
    max(AGE) as age, max(DATE) as date
    for names in ( 
        {% for pivot_col in pivot_cols %}
        '{{pivot_col}}'{% if not loop.last %}, {% endif %}
        {% endfor %}
    )
)
select
    COUNTRY,
    {% for pivot_col in pivot_cols %}
    age_{{pivot_col}} as {{pivot_col}},
    date_{{pivot_col}} as {{pivot_col}}_date{% if not loop.last %}, {% endif %}
    {% endfor %}
from pivot

代码说明 #

1. 在pivot函数内同时声明两个聚合字段：max(AGE) as age和max(DATE) as date，透视后会生成age_xxx（xxx为姓名）和date_xxx格式的临时列。
2. 最后通过select语句重命名列，将age_xxx改为姓名本身，date_xxx改为xxx_date，完全匹配期望的输出结构。

内容的提问来源于stack exchange，提问作者l217