HOWTO · PostgreSQL

How to Transpose Columns to Rows in PostgreSQL

Learn how to turn column-oriented values into rows in PostgreSQL with unnest(), and when crosstab() is the reverse operation.

On this page

To turn values stored in several columns into rows in PostgreSQL, first place the values in arrays and expand them with unnest(). The function emits array elements as rows. crosstab() does the opposite kind of reshaping: it pivots categorized rows into columns, so it is not a columns-to-rows solution.

For example, the four course values val1 through val4 can be arranged as one value per row, associated with the student and degree in the same position. This is useful when a wide record needs to be processed or displayed as a sequence of attribute-value pairs. The exact query depends on whether the values already live in arrays or in separate table columns.

Transpose Columns to Rows in PostgreSQL

A table with one column per attribute is a wide representation. To reshape selected values, construct arrays in a consistent order and expand them together. The first array can hold labels, while the other arrays hold corresponding values. Each output row then contains the label and the values at that position.

PostgreSQL’s unnest() is an array-expansion function, not a general operation that automatically discovers and transposes every column in a table. You choose the columns and their order in the query. This makes the transformation explicit and helps avoid accidentally pairing values from different attributes.

Create the Example Table

The following SQL defines the sample table and inserts the records used below. Std_no is left nullable to match the supplied data: the insert statements omit it, so these rows have no student number. StdName, StdDegree, and CourseName hold two students’ four degree and course-value pairs.

Use this schema: CREATE TABLE Student (Std_no int, StdName text, StdDegree text, CourseName text);

Insert the sample rows:

INSERT INTO Student (StdName, StdDegree, CourseName) VALUES ('test1','att1','val1'), ('test1','att2','val2'), ('test1','att3','val3'), ('test1','att4','val4'), ('test2','att1','val5'), ('test2','att2','val6'), ('test2','att3','val7'), ('test2','att4','val8');

The rows are grouped by StdName; for test1, the degree-to-course mapping is att1 to val1 through att4 to val4. Keep that ordering when building parallel arrays so each label remains paired with its intended value.

Use crosstab() to Pivot Rows into Columns

crosstab() is useful when the input consists of rows with a row identifier, category, and value, and the desired result has one output column per category. That is a row-to-column pivot, the reverse direction from turning columns into rows. It is included here to distinguish the similarly named reshaping task.

The function is provided by PostgreSQL’s tablefunc extension. A typical setup is CREATE EXTENSION IF NOT EXISTS tablefunc;. Its source query must return a row name, category, and value, and the result needs a declared output record type. For example, the form crosstab(source_sql, category_sql) uses one query for the data and another to define the category order. The output columns must match the declared result shape; crosstab() does not infer a variable number of columns for a fixed query result.

Use crosstab() when categories should become columns in a report. Do not use it for the columns-to-rows transformation in this article: its purpose and output direction differ.

Use unnest() to Turn Arrays into Rows

unnest(anyarray) expands an array into a set of rows. To turn several parallel arrays into aligned columns, use the multi-array form in the FROM clause and give the resulting columns aliases. Here is a Python illustration of positional expansion; it is a standalone example, not a PostgreSQL query:

labels = ["StdName", "StdDegree", "CourseName"]
values = ["test1", "att1", "val1"]
for label, value in zip(labels, values):
    print(f"{label}: {value}")
StdName: test1
StdDegree: att1
CourseName: val1

To expand columns from the sample table, select one source row and pass its fields alongside an array of their labels. This PostgreSQL query selects the test1 row with degree att1, then returns one attribute-value pair per selected column:

SELECT t.attribute, t.value FROM Student AS s CROSS JOIN LATERAL unnest(ARRAY['StdName','StdDegree','CourseName']::text[], ARRAY[s.StdName, s.StdDegree, s.CourseName]::text[]) AS t(attribute, value) WHERE s.StdName = 'test1' AND s.StdDegree = 'att1';

The output has two columns, attribute and value, and three rows: StdName with test1, StdDegree with att1, and CourseName with val1. Each value comes from the same sample-table row, so no student number is introduced. The PostgreSQL query is presented as SQL and was not executed in the supplied Python runtime.

The two arrays use compatible text elements: one holds the labels and the other holds the selected column values. PostgreSQL’s multi-array unnest() pads shorter arrays with NULL values to the length of the longest array. Make the arrays equal in length when each position must represent a complete record; otherwise, handle those nulls deliberately.

A different form places unnest() calls in the SELECT list. For a single array, SELECT unnest(ARRAY['att1','att2','att3','att4']) AS degree; produces one result row per element. Multiple set-returning functions in a select list are more difficult to reason about and have version-sensitive behavior; use the multi-array FROM form when aligned columns are intended. It states the relationship between arrays explicitly and gives the output columns clear names.

The example is deliberately small. To reshape values from a real table, select the needed columns into arrays in a defined order, or use a lateral query when each source row has its own array values. unnest() expands arrays; it does not automatically convert arbitrary table columns into a normalized set of attribute-value rows.

Choose the Right Method

Use unnest() when you have arrays whose elements should become rows, including parallel arrays that should appear as aligned output columns. Keep their lengths and element ordering consistent, and name the output columns with an alias list. If the source is a regular table with unrelated scalar columns, specify which fields to package into arrays or build a query suited to that table’s structure.

Use crosstab() when categorized input rows should become output columns. The direction is the reverse: crosstab() pivots rows into columns, while unnest() expands array elements into rows. Choosing based on the desired output shape prevents applying a pivot function to the wrong task.