Shape / table · All operations

Gather rows into columns

Flatten a row group into columns, or spread columns back to rows.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

a new grain: each row stands for a group of input rows

Level

several clauses of SQL, or a window

The DuckDB step

Flatten a row group into columns

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE result AS
SELECT customer, list(product ORDER BY order_date) AS products,
       products[1] AS first_product, products[2] AS second_product
FROM orders GROUP BY customer;
SELECT * FROM result ORDER BY ALL;
SELECT CASE WHEN ((SELECT count(*) = 8 AND max(first_product) FILTER (WHERE customer = 'Alice Ng') = 'widget' AND max(second_product) FILTER (WHERE customer = 'Alice Ng') = 'gizmo' FROM result)) THEN 'as expected' ELSE error('the orders of one customer did not flatten in date order') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the orders of one customer did not flatten in date order”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Gather key-value rows into columns

Turn rows of keys and values into one row per group with a column per key.

Flatten consecutive rows into one

Gather every N consecutive rows, or each run of rows, into the columns of a single row.

Treat row groups as records

Treat the rows under one leading value as one record, so later operations apply per record.

Spread repeated columns back into rows

Turn a set of repeated columns into one row per column, with a type field and value fields.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)

Make Columnsread 2026-09-27
Wrap rows of data into a set number of columns, filled horizontally or vertically

Apache Hop (3)

Row Denormaliserread 2026-09-27
Turn key-value rows into one row per group with a column per key
Row Flattenerread 2026-09-27
Flatten consecutive rows into the repeated fields of a single row
Row Normaliserread 2026-09-27
Turn repeated columns into rows with a type field and value fields

KNIME Analytics Platform (1)

Column to Gridread 2026-09-27
Rearrange a column's values into a fixed number of grid columns, filling row by row

OpenRefine (2)

Show as recordsread 2026-09-27
Treat consecutive rows under one first-column value as one record, so operations apply per record
Transpose cells in rows into columns…read 2026-09-27
Turn every N consecutive rows of one column into N columns of one row

Pentaho Data Integration (3)

Row Denormaliserread 2026-09-27
Turn key-value rows into columns per group; needs input sorted on the group fields
Row Flattenerread 2026-09-27
Flatten consecutive rows into fields of one row, in arrival order
Row Normaliserread 2026-09-27
Turn repeated columns back into rows

Background

Converting a narrow table into a wide table is generally referred to as pivoting in the context of data transformations.^[1] A narrow table has one column containing all the values and another column listing the context of each value.^[1] A wide table presents each different data variable in a separate column.^[1] In DuckDB, the UNPIVOT statement is the inverse of the PIVOT statement, stacking multiple columns into fewer columns.^[2]^[3]

When to use it

A narrow table is often easier to implement, since adding a new field needs no change to the table's structure, but it can be harder for people to understand.^[1] Widen a table where a person needs to read one row per thing, and keep it narrow while new kinds of value may still arrive. (Meridian's view)

In OpenRefine, Columnize by key/value columns reshapes a dataset with key and value columns, so the repeating strings in the key column become new column names.^[4] In OpenRefine, Transpose cells in rows into columns asks how many rows to transpose, and Columnize by key/value columns is the one to try when a dataset has no predictable number of cells per intended row.^[4] In OpenRefine, when a new row would have several values for one key, Columnize groups those values on consecutive rows to form a record structure.^[4]

In Apache Hop, the Row Denormaliser needs its input ordered by the grouping keys, and a Sort rows transform can be used first if needed.^[5] In Apache Hop's Row Denormaliser, when a key is not unique for its group, an aggregation method is specified to settle the collision.^[5] In Apache Hop's Row Denormaliser, key-value pairs are most commonly strings, so values must often be converted to Integer, Number or Date.^[5] Decide before you pivot what should happen when one group carries the same key twice, rather than letting the tool's default decide. (Meridian's view)

In DuckDB, order-sensitive aggregates such as first, last, list and string_agg can have non-deterministic results unless their arguments are ordered.^[6] When rows are gathered by position, name the column that orders them, so the same data always lands in the same columns. (Meridian's view)

In Apache Hop's Row Normaliser, when the input fields filling one new field do not share a data type, the new field is a String and every value is converted to text.^[7]

The method

In DuckDB, the PIVOT statement separates the distinct values within a column into their own columns, each calculated by an aggregate function over the rows matching that value.^[2] In DuckDB, the USING clause of PIVOT defaults to count(*) when it is not included.^[2] In DuckDB, using the first aggregate function in PIVOT changes the orientation of the data without aggregating, and works well for pivoting out a text column.^[2]

In DuckDB, the list aggregate returns a LIST containing all the values of a column, and its result is affected by ordering.^[6] In DuckDB, an ORDER BY clause written after the last argument of an aggregate call sorts the values before the function is applied.^[6] In DuckDB, list[index] extracts a single list element using a 1-based index.^[8]

OpenRefine understands records based on the content of the first column, which it calls the key column.^[9] In OpenRefine's records mode, a single record may contain more than one row.^[9] OpenRefine assigns a unique key behind the scenes, so records do not need a unique identifier in the key column.^[9]

In Apache Hop, the Row Normaliser writes one row per type for every row it reads, with each input field going into the new field it names on the row of its type.^[7] In DuckDB, UNPIVOT in the basic case stacks columns into a NAME column holding each source column's name and a VALUE column holding its value.^[3]

Compared with the alternatives

Wide and narrow presentations are also called wide and long, un-stacked and stacked, or pivoted and unpivoted, with the terms varying by community and software.^[1] In DuckDB, the ON clause of PIVOT is equivalent to the columns parameter of a spreadsheet pivot table, and the USING clause to its values parameter.^[2]

Sources

[1]Wide and narrow data - Wikipediasecondary source
[2]PIVOT Statement – DuckDBdocumentation
[3]UNPIVOT Statement – DuckDBdocumentation
[4]Transposing | OpenRefinedocumentation
[5]Row Denormaliser | Apache Hopdocumentation
[6]Aggregate Functions – DuckDBdocumentation
[7]Row Normaliser | Apache Hopdocumentation
[8]List Functions – DuckDBdocumentation
[9]Exploring data | OpenRefinedocumentation