halpo / pivot Goto Github PK

for databases that don't have native PIVOT support. Requires two queries (in general) and repeated left_join() or union_all() queries. I think it should be possible to rely solely on dbplyr verbs, no need to generate SQL manually (though it might be smaller or easier to parse/optimize if generated manually).

Seems worth checking if implementations exist elsewhere.

The spread() verb won't work for data frames if there are duplicates in the keys. It is unfortunate that PIVOT() seems to require an aggregation function, I haven't found a way to write this operation so that no aggregates are performed (but an error is returned instead). Perhaps with a user-defined function?

Either way, FIRST_VALUE() seems to be a slightly better choice than MAX(), because it might also work for non-numeric data.

library(tidyverse)
data <- tibble(a = c(1, 1, 2), b = a, c = 4:6)
data %>%
  spread(b, c)
#> Error: Duplicate identifiers for rows (1, 2)

Created on 2018-05-20 by the reprex package (v0.2.0).