Shape / table · All operations

Transpose a table

Swap rows and columns.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
the same cells, rearranged
Level
several clauses of SQL, or a window

The DuckDB step

Swap rows and columns

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

SQL
CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
PIVOT (UNPIVOT (SELECT customer, lat, lon FROM customers) ON lat, lon INTO NAME measure VALUE reading)
ON customer USING first(reading) GROUP BY measure;
SELECT * FROM result ORDER BY ALL;
SELECT CASE WHEN ((SELECT count(*) = 2 AND max("Alice Ng") FILTER (WHERE measure = 'lat') = 52.52 AND max("Frank Moore") FILTER (WHERE measure = 'lon') = -0.13 FROM result)) THEN 'as expected' ELSE error('the customers did not become columns and the measures rows') 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 customers did not become columns and the measures rows”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Duckle (1)
  • Transposeread 2026-09-25

    swap rows and columns with UNPIVOT then PIVOT

KNIME Analytics Platform (1)
Polars (1)
Power Query M (1)
RapidMiner Studio (1)
  • Transposeread 2026-09-27

    Swap rows and columns

VisiData (1)
  • transposeread 2026-09-23

    Open sheet with rows and columns swapped

Background

In Power Query, transposing a table rotates it 90 degrees, turning its rows into columns and its columns into rows.[1] In linear algebra, transposition flips a matrix over its diagonal, switching the row and column indices of the matrix.[2] In Excel, the TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa.[3]

When to use it

In Power Query, only the contents of a table are transposed, so the column headers of the original table are lost.[1] In Power Query, the new columns of a transposed table are named Column followed by a sequential number.[1] In Power Query, when the headers needed after a transpose are in the first row, the Use first row as headers step promotes that row to headers.[1] Before transposing, decide which column will name the new columns, and keep the old column names as a column of their own, so no label is lost on the way. (Meridian's view)

In pandas, transposing a DataFrame with mixed dtypes gives a DataFrame in which every column has the object dtype.[4] In KNIME, the Table Transposer gives a new column the most specific base type, applied to all cells in one row.[5] In RapidMiner, if at least one nominal attribute is part of the input, the type of every new attribute of the transposed table is nominal.[6] In RapidMiner, transposing a transposed table may not give back the original, because the value types of the two can differ.[6] Transpose a table whose columns share one type, or expect the values to come out as text or as one common type and to need casting back. (Meridian's view)

In Polars, DataFrame.transpose is described as a very expensive operation.[7] In RapidMiner, the Transpose operator produces a copy of the data in main memory, so it should not be used on very large data sets.[6] Keep transposing for small tables, such as a summary or a single record of names and values that a person will read across. (Meridian's view)

In Excel, copying and pasting with the Transpose option creates duplicates that are not updated when the original cells change.[3] In Excel, the TRANSPOSE function is a formula that can be used instead of pasting when duplicated data is not wanted.[3]

The method

The transpose of a matrix was introduced in 1858 by the British mathematician Arthur Cayley.[2] In linear algebra, the element in row i and column j of a transpose is the element in row j and column i of the original matrix.[2] In linear algebra, the transpose of an m × n matrix is an n × m matrix.[2] In linear algebra, repeating the transpose on a transposed matrix returns the elements to their original position.[2] In Excel, the TRANSPOSE function uses the first row of an array as the first column of the new array, the second row as the second column, and so on.[3]

In KNIME, the Table Transposer takes the new column names from the old RowIDs, and the new row identifiers are the old column names.[5] In RapidMiner, when an id attribute is part of the input, the ids become the names of the new attributes and the old attribute names become the values of a new id attribute.[6] In Polars, DataFrame.transpose can add the old column names as a first column and can name the new columns from an existing column.[7]

In DuckDB, the UNPIVOT statement stacks columns into a NAME column, holding the name of each source column, and a VALUE column, holding its value.[8] In DuckDB, the PIVOT statement separates the distinct values within a column into their own columns, calculating their values with an aggregate function.[9] In DuckDB, the first aggregate function can be used in a PIVOT to change the orientation of the data without aggregating it.[9] To transpose in SQL, unpivot the columns into name and value pairs, then pivot on the column that identifies each row, with first as the aggregate. (Meridian's view)

Compared with the alternatives

In DuckDB, the UNPIVOT statement is the inverse of the PIVOT statement.[8] In DuckDB, the PIVOT statement by default retains all columns not specified in its ON or USING clauses.[9] Reach for a pivot when the values of one column should become columns and the others should stay as they are, and for a transpose when every row should become a column. (Meridian's view)

Sources

  1. [1]Transpose a table - Power Query | Microsoft Learndocumentation
  2. [2]Transpose - Wikipediasecondary source
  3. [3]TRANSPOSE functiondocumentation
  4. [4]pandas.DataFrame.transpose — pandas 3.1.1 documentationdocumentation
  5. [5]Table Transposer – KNIME Community Hubdocumentation
  6. [6]Transpose - Rapidminer Documentationdocumentation
  7. [7]polars.DataFrame.transpose — Polars documentationdocumentation
  8. [8]UNPIVOT Statement – DuckDBdocumentation
  9. [9]PIVOT Statement – DuckDBdocumentation