How Can I Transpose a PostgreSQL Table's Columns into Rows?

Transposing Table: Columns to Rows

Problem Statement:

Consider a PostgreSQL output table with the following format:

Sl.no    username    Designation    salary   etc..
 1        A           XYZ            10000    ...
 2        B           RTS            50000    ...
 3        C           QWE            20000    ...
 4        D           HGD            34343    ...

The goal is to transpose the table, converting the columns into rows, resulting in the following output:

Sl.no            1       2        3       4       ...
 Username        A       B        C       D       ...
 Designation     XYZ     RTS      QWE     HGD     ...
 Salary          10000   50000    20000   34343   ...

Solution:

To transpose the table and convert columns to rows, you can utilize the following PostgreSQL function:

SELECT
   unnest(array['Sl.no', 'username', 'Designation','salary']) AS "Columns",
   unnest(array[Sl.no, username, value3Count,salary]) AS "Values"
FROM view_name
ORDER BY "Columns"

Explanation:

• unnest(array['Sl.no', 'username', 'Designation','salary']) creates a new row for each column in the original table, effectively creating column names.
• unnest(array[Sl.no, username, value3Count,salary]) creates a new row for each value in the original table, corresponding to the column names.
• ORDER BY "Columns" ensures that the rows are ordered by column names.

This query will produce the desired transposed table, where the columns are now rows.

The above is the detailed content of How Can I Transpose a PostgreSQL Table's Columns into Rows?. For more information, please follow other related articles on the PHP Chinese website!