tests/testthat/_snaps/verb-pivot-longer.md

In dbplyr: A 'dplyr' Back End for Databases

can pivot all cols to long

Code
  lazy_frame(x = 1:2, y = 3:4) %>% tidyr::pivot_longer(x:y)
Output
  <SQL>
  SELECT 'x' AS `name`, `x` AS `value`
  FROM `df`

  UNION ALL

  SELECT 'y' AS `name`, `y` AS `value`
  FROM `df`

can add multiple columns from spec

Code
  pv
Output
  <SQL>
  SELECT 11 AS `a`, 13 AS `b`, `x` AS `v`
  FROM `df`

  UNION ALL

  SELECT 12 AS `a`, 14 AS `b`, `y` AS `v`
  FROM `df`

preserves original keys

Code
  pv
Output
  <SQL>
  SELECT `x`, 'y' AS `name`, `y` AS `value`
  FROM `df`

  UNION ALL

  SELECT `x`, 'z' AS `name`, `z` AS `value`
  FROM `df`

can drop missing values

Code
  lazy_frame(x = c(1, NA), y = c(NA, 2)) %>% tidyr::pivot_longer(x:y,
  values_drop_na = TRUE)
Output
  <SQL>
  SELECT `q01`.*
  FROM (
    SELECT 'x' AS `name`, `x` AS `value`
    FROM `df`

    UNION ALL

    SELECT 'y' AS `name`, `y` AS `value`
    FROM `df`
  ) AS `q01`
  WHERE (NOT((`value` IS NULL)))

can handle missing combinations

Code
  sql
Output
  <SQL>
  SELECT `q01`.*, NULL AS `y`
  FROM (
    SELECT `id`, '1' AS `n`, `x_1` AS `x`
    FROM `df`
  ) AS `q01`

  UNION ALL

  SELECT `id`, '2' AS `n`, `x_2` AS `x`, `y_2` AS `y`
  FROM `df`

can override default output column type

Code
  lazy_frame(x = 1) %>% tidyr::pivot_longer(x, values_transform = list(value = as.character))
Output
  <SQL>
  SELECT 'x' AS `name`, CAST(`x` AS TEXT) AS `value`
  FROM `df`

values_transform can be a formula

Code
  lazy_frame(x = 1) %>% tidyr::pivot_longer(x, values_transform = list(value = ~
    as.character(.x)))
Output
  <SQL>
  SELECT 'x' AS `name`, CAST(`x` AS TEXT) AS `value`
  FROM `df`

values_transform is validated

Code
  (expect_error(tidyr::pivot_longer(df, x, values_transform = 1)))
Output
  <error/rlang_error>
  Error in `dbplyr_pivot_longer_spec()`:
  ! `values_transform` must be `NULL`, a function, or a named list of functions.
Code
  (expect_error(tidyr::pivot_longer(df, x, values_transform = list(~.x))))
Output
  <error/rlang_error>
  Error in `dbplyr_pivot_longer_spec()`:
  ! All elements of `values_transform` must be named.

can pivot to multiple measure cols

Code
  pv
Output
  <SQL>
  SELECT 1.0 AS `row`, `x` AS `X`, `y` AS `Y`
  FROM `df`

.value can be at any position in names_to

Code
  value_first
Output
  <SQL>
  SELECT `i`, 't1' AS `time`, `y_t1` AS `y`, `z_t1` AS `z`
  FROM `df`

  UNION ALL

  SELECT `i`, 't2' AS `time`, `y_t2` AS `y`, `z_t2` AS `z`
  FROM `df`

Code
  value_second
Output
  <SQL>
  SELECT `i`, 't1' AS `time`, `t1_y` AS `y`, `t1_z` AS `z`
  FROM `df`

  UNION ALL

  SELECT `i`, 't2' AS `time`, `t2_y` AS `y`, `t2_z` AS `z`
  FROM `df`

can repair names

Code
  out <- df %>% tidyr::pivot_longer(c(x, y), names_repair = "unique")
Message
  New names:
  * `name` -> `name...2`
  * `value` -> `value...3`
  * `name` -> `name...4`
  * `value` -> `value...5`

values_ptype is not supported

Code
  lazy_frame(x = 1:2, y = 3:4) %>% tidyr::pivot_longer(x:y, values_ptypes = character())
Condition
  Error in `tidyr::pivot_longer()`:
  ! Argument `values_ptypes` isn't supported on database backends.

cols_vary is not supported

Code
  lazy_frame(x = 1:2, y = 3:4) %>% tidyr::pivot_longer(x:y, cols_vary = "fastest")
Condition
  Error in `tidyr::pivot_longer()`:
  ! Argument `cols_vary` isn't supported on database backends.

dbplyr documentation built on Oct. 26, 2023, 9:06 a.m.