How to Transpose a Table in PostgreSQL: Converting Columns to Rows?

Transposing Tables: Converting Columns to Rows

When working with tabular data, it can be useful to transpose a table, converting its columns to rows. This can facilitate data visualization, analysis, and integration with other datasets.

Consider the following PostgreSQL table, which contains employee information:

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

To transpose this table, we can employ the following steps:

1. Unnest the Column Names:
   Using the unnest() function, we can extract the column names into individual rows:

   unnest(array['Sl.no', 'username', 'Designation','salary']) AS "Columns"

2. Unnest the Data Values:
   Similarly, we can extract the data values in each row into individual rows:

   unnest(array[Sl.no, username, value3Count,salary]) AS "Values"

3. Join the Extracted Lists:
   Finally, we can join the extracted column names and data values to recreate the transposed table:

   SELECT
      "Columns",
      "Values"
   FROM transposed_table
   ORDER BY "Columns"

The result of this query will be:

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

This transposed table now arranges the employee information in rows, with the original column names as the first column. This format can be more convenient for certain operations, such as data visualization or comparing employee attributes across different categories.

The above is the detailed content of How to Transpose a Table in PostgreSQL: Converting Columns to Rows?. For more information, please follow other related articles on the PHP Chinese website!