How to Transpose Columns to Rows in PostgreSQL?

There are multiple events when we want to display data in a transformed way. This transformation includes changing the data rows into columns and vice versa. We can transpose the column to rows mainly by using two methods. This article comprises the methods to transpose columns to rows that are:

● Using crosstab() function to transpose columns to rows in PostgreSQL

● Using unnest() function to transpose columns to rows in PostgreSQL

How to Transpose Columns to Rows in PostgreSQL?

We can get the transposed table, by two approaches i.e. converting the columns of a Postgres table into rows and data rows into columns. Let’s see what these two methods are and how we can utilize/implement them to transpose columns to rows.

Method 1: Using crosstab() Function

The crosstab() function is supported by PostgreSQL version 9.7 and above. We can use the crosstab() function to get the transpose of a table. This function is used in different ways, with different parameters. The PostgreSQL documentation provides a comprehensive description of these variants.

For the scope of this tutorial, just the “crosstab(sql text)” will be discussed. The parameter of this function is a SQL query. We will move toward an example so that the concept is more clear to us.

Example: Transpose Columns to Rows Using crosstab() Function

Let's consider a table named “students_marks” containing the marks of the students.

The names of some students and their respective marks occurred more than one time because some of the students have attempted the test multiple times as two attempts were allowed.

Now if we want to see which student attempted the test both times and how many marks they got on each attempt we will write the following query:

SELECT *
 FROM crosstab($$
  select student_name::text,student_id::text, test_marks::text
  from students_marks
  order by 1,2
   $$)
 AS   registration(student_name text, Attempt_1 text, Attempt_2 text);

In the above code, the crosstab() function takes a SQL query as a parameter which comprises a select statement. This select statement must have 3 columns specified those are:

○ The first one specifies each row for the returning table.

○ The second column identifies the category.

○ The last one specifies the value of each cell.

These three columns have to be of TEXT data type. In my case they were not so I did type casting of them into text data type. The last line of the query specifies the new columns of the transposed table.

IMPORTANT: Here one thing is to be noted that if you have never used the crosstab() function previously, running the above syntax will return an error like this:

But no need to worry! The solution to this error/problem is creating an extension called “tablefunc”. This error arises because this extension is missing. So we need to create this extension first. Execute the following query to create the extension:

CREATE EXTENSION tablefunc;

By executing this query you will successfully create the extension in your PostgreSQL like this:

After doing this your query for the crosstab() function will execute properly with no error. The output for the query is given as:

The resulting table gives the transposed table. The marks of a student are arranged in the form of a row with respect to his/her attempt. If the student attempted the test once, the second attempt column gets the value NULL.

This is how we use the crosstab() function to transpose columns to rows. Let's move towards the second method.

Method 2:Using unnest() Function

The unnest() function can also be utilized to achieve a similar purpose. We can transpose the columns to rows effectively in PostgreSQL using this unnest() function. The query for the transposing column to rows is written like this:

SELECT unnest('{StudentID, StudentName, Address,city}'::text[]) AS col
  , unnest('{1,John,13th Street. 47 W ,New York}'::text[]) AS row_1
  , unnest('{2,Alex,24th Street. 32 E , San Diego}'::text[]) AS row_2
  , unnest('{3,Peter,6th Street. 23 W, San Francisco}'::text[]) AS row_3
  , unnest('{4,Williams,4430 Davenport Street Northwest , Boston}'::text[]) AS row_4;

The function will return the given parameters as columns. The output for clarity is given below:

So using these two above-mentioned methods, we can transpose the columns into rows and vice versa.

Conclusion

We can transpose columns into rows as per our need in PostgreSQL. There are 2 functions that can be used for this i.e. the crosstab() function and the unnest() function. These functions help us transform the table by returning the transposed table. In this blog, we have understood in detail, the functioning of these functions work using practical examples.

How to Transpose Columns to Rows in PostgreSQL?