Shape / columns · All operations

Apply one formula to many columns

A variant of Add a calculated column

Apply the same expression to each of a set of columns, creating or updating one column per input.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

One formula applied to each of several columns

SQL
CREATE TEMP TABLE scores AS SELECT * FROM (VALUES (1, 10, 20, 30), (2, 5, NULL, 15)) t(id, q1, q2, q3);
CREATE TEMP TABLE result AS
SELECT id, COLUMNS('^(q\d)$') * 2 AS 'double_\1' FROM scores;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'double_q1', 'double_q2', 'double_q3'] AND (SELECT [double_q1, double_q2, double_q3] FROM result WHERE id = 1) = [20, 40, 60] AND (SELECT double_q2 IS NULL AND double_q3 = 30 FROM result WHERE id = 2)) THEN 'as expected' ELSE error('each of q1, q2 and q3 was not doubled into a column of its own') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “each of q1, q2 and q3 was not doubled into a column of its own”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Alteryx Designer (1)
  • Multi-Field Formularead 2026-09-27

    Apply one expression across many fields to create or update them

KNIME Analytics Platform (1)
RapidMiner Studio (1)
  • Generate Function Setread 2026-09-27

    Generate new columns by applying a chosen set of functions to all numeric columns

Background

DuckDB's COLUMNS expression selects columns by a list, a regular expression or a lambda, and runs the surrounding expression on each one.[1] In dplyr, across() applies the same transformation to multiple columns inside functions such as mutate() and summarise().[2]

When to use it

Reach for it when several columns need the same treatment, such as rounding every amount or trimming every text field, so the rule cannot drift between hand-written copies. (Meridian's view) In DuckDB, COLUMNS(*) applies the expression to every column of the FROM clause, and COLUMNS(* EXCLUDE (col)) leaves the named columns out.[1]

dplyr's across() names its outputs with the .names glue specification, whose default for a single function is {.col}, the input column's own name.[2] dplyr's mutate() modifies an existing column when a new column is given the same name.[3] Give across() a .names pattern inside mutate() whenever the original columns must be kept alongside the results. (Meridian's view)

The method

DuckDB's COLUMNS can rename its results from the capture groups of its regular expression, which are one-indexed, with \0 standing for the original column name.[1] Several COLUMNS expressions can be combined in one expression as long as they contain the same star expression, as in COLUMNS(*) + COLUMNS(*).[1]

dplyr's across() supersedes the family of scoped variants such as summarise_at(), summarise_if() and summarise_all().[2]

Compared with the alternatives

dplyr's if_any() and if_all() apply one predicate to a selection of columns but combine the results into a single logical vector.[2] A formula applied to many columns keeps one result per column; when the results should be combined into one value per row, the operation is an aggregate across columns instead. (Meridian's view)

Sources

  1. [1]Star Expression – DuckDBdocumentation
  2. [2]Apply a function (or functions) across multiple columns — across • dplyrdocumentation
  3. [3]Create, modify, and delete columns — mutate • dplyrdocumentation