How Can I Transpose Tables (Columns to Rows) in PostgreSQL Using `unnest()`?

Transposing Tables: Converting Columns to Rows in PostgreSQL

Transforming tabular data by rotating columns into rows can be a valuable operation in data manipulation. In PostgreSQL, there are methods to achieve this transposition effectively. One such method involves leveraging the unnest() function.

Solution Using unnest()

To transpose a table, we can use a combination of unnest() and ARRAY aggregation. The following query demonstrates the technique:

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

• The unnest() function unpacks arrays into individual elements. In this case, it extracts the column names and corresponding values from the original table.
• The ARRAY aggregation creates arrays to hold the column names and values for each row.
• The ORDER BY clause sorts the results by column names to ensure that the transposed table has the desired structure.

Output:

The query returns a transposed table with the following format:

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

Note: The original table name in the query may vary depending on your actual table name.

The above is the detailed content of How Can I Transpose Tables (Columns to Rows) in PostgreSQL Using `unnest()`?. For more information, please follow other related articles on the PHP Chinese website!