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`
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`
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`
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)))
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`
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`
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 validatedCode
(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.
Code
pv
Output
<SQL>
SELECT 1.0 AS `row`, `x` AS `X`, `y` AS `Y`
FROM `df`
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`
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`
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.
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.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.