How Can I Transpose a Table in PostgreSQL Using UNNEST()?

Transpose a Table: Embracing Rows as Columns

The challenge of transposing a table to convert columns into rows presents a unique opportunity for data manipulation in PostgreSQL. Instead of relying on row-oriented processing, this transformation requires a shift towards column-oriented techniques.

To achieve this transposition, PostgreSQL introduces the powerful UNNEST() function. By unravelling an array of column names, the UNNEST() function unravels the table's columns into rows, creating a foundation for the desired output.

The provided SQL query leverages the UNNEST() function to construct two arrays: one containing the column names and the other containing the corresponding row values. Subsequently, the Cartesian product of these arrays generates new rows where each column name is aligned with its respective value.

By employing ORDER BY, this query ensures the transposed table preserves the original column order. Consequently, the resulting output aligns with the desired format, where columns are converted into header rows, presenting the data in a user-friendly and readable manner.

The above is the detailed content of How Can I Transpose a Table in PostgreSQL Using UNNEST()?. For more information, please follow other related articles on the PHP Chinese website!