How Can I Transpose a PostgreSQL Table Using `unnest()`?

Transpose a Table by Converting Columns to Rows

When working with tables in PostgreSQL, there may be situations where you need to transpose a table, essentially converting columns into rows. This is a useful transformation for various data analysis purposes.

Problem Statement:

You have a PostgreSQL function or table that outputs data in 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	...

You want to transpose this table so that the output appears in the following format:

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

Solution:

To achieve this transposition, you can use the unnest() function from PostgreSQL. This function allows you to extract elements from an array and treat them as individual rows.

Here's the query that will transpose your table:

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 array[...] constructs arrays containing the column names and the corresponding values for each row.
• The unnest() function extracts each element from the arrays and treats them as separate rows.
• The ORDER BY clause ensures that the transposed table has the original column order.

This query will output the transposed table in the desired format.

Reference:

The unnest() function is documented in the PostgreSQL documentation under "convertingColumnsToRows."

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