Pivot Macro for Dataform

[Henning1]

Hello everyone,

I am currently evaluating Dataform and wondering if it is possible to dynamically pivot data using a function similar to this dbt macro:

https://towardsdatascience.com/mastering-pivot-tables-in-dbt-832560a1a1c5

https://github.com/dbt-labs/dbt-utils#pivot-source

[lsolatorio]

Hi @Henning1, 

Welcome and thank you for reaching out to out community.

You can explore writing a query using the native SQL PIVOT operator or add a custom SQL operation in Dataform workflow.

FROM from_item[, ...] pivot_operator

pivot_operator:
  PIVOT(
    aggregate_function_call [as_alias][, ...]
    FOR input_column
    IN ( pivot_column [as_alias][, ...] )
  ) [AS alias]

as_alias:
  [AS] alias

Dataform can execute custom SQL operations that don't fit into the Dataform model of publishing a table or writing an assertion. You can define custom SQL commands for Dataform to execute in BigQuery.

---

Adding a couple more resource that can be useful for your usecase.

• Create and execute a SQL workflow in Dataform
• Create Dataform SQL workflows with JavaScript

Hope this helps.

[Henning1]

Hi @lsolatorio ,

Thanks a lot for your reply but the standard pivot operator doesn't let me dynamically create the pivot_columns.
I am looking for a macro that replicates this functionality:

EXECUTE IMMEDIATE FORMAT("""
SELECT * FROM ${ ref('table')}
PIVOT (aggregate_function_call FOR input_column IN %s);
""", (SELECT CONCAT("(", STRING_AGG(DISTINCT CONCAT("'", input_column, "'"), ','), ")") FROM ${ ref('table')}))

which seems to work in Dataform in preview but fails during execution (I assume because EXECUTE IMMEDIATE can't be executed inside CTE's)

[Wiktor_Jurek]

Looking to implement this, too. Any news, let me know.

[kolban]

I'd like to examine this question ... but sadly my SQL foo is poor.  Without reference to Dataform, can you supply  a trivial input table description and the SQL statement you would like executed with the parameters that you would like in a potential Dataform macro and, ideally, what the resulting table would look like?

[Wiktor_Jurek]

It's standard pivot functionality just as you would use in a spreadsheet. This is how dbt have implemented it.

It's changing a tall data table into a wide data table based upon a dimension column and an aggregation of metrics columns. The EXECUTE IMMEDIATE statement above is pretty much the only way to do this dynamically, and dataform doesn't support those, unfortunately.

[Henning1]

@kolban: You can find a trival example table in the towardsdatascience article at the beginning of this thread. Or also here: https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/pivot.sql
My question is rather if it is possible and perhaps planned to develop a (JS) macro collection for Dataform that is similar to dbt-utils which also includes the pivot macro?
I found so far only this grouping function:

// Grouping
function groupBy(n) {
  var indices = [];
  for (var i = 1; i <= n; i++) {
    indices.push(i);
  }
  return `GROUP BY ${indices.join(", ")}`;
}

[Wiktor_Jurek]

I've not come up with a macro for it, but a workaround is using an operation within a .sqlx file. I've written an article about how this could be achieved. Feedback welcome!