How to Transpose Columns to Rows in PostgreSQL

1. Transpose Columns to Rows
2. Create a Table in PostgreSQL
3. Use the crosstab() Function to Transpose Columns to Rows in PostgreSQL
4. Use the unnest() Function to Transpose Columns to Rows in PostgreSQL
5. Conclusion

This article has been specially articulated to discuss a method using columns provided in the input to be transformed into rows.

There are multiple instances where data needs to be displayed in a different format. This article describes transforming the data from regular columns to rows.

To summarize the entire article, the problem that requires a solution has been discussed in one section. Then, the two methods, crosstab() and unnest(), were introduced to help solve the problem.

Lastly, examples are discussed for each method to provide a better insight into both methods.

Transpose Columns to Rows

The problem under discussion in this article is how to transpose columns to rows. For instance, you have the following table:

Std_no.	Name	Degree
  1		 A		  W
  2 	 B        X
  3  	 C        Y
  4  	 D        Z

This article describes how to convert the columns in the table to rows. Therefore, the expected solution should be:

Std_no.		1		2		3		4
Name		A		B		C		D
Degree		W		X		Y		Z

The article mentions the procedure to convert the columns into rows. The input can be in the form of table columns or arrays.

Both of the possibilities are catered to in the article.

Create a Table in PostgreSQL

The same table and values will be used to make things easier to understand throughout the article. This will shift the focus to understanding the functions rather than spending time on the different schemas.

The following query is used to define a table:

CREATE TABLE Student(Std_no int, StdName TEXT, StdDegree TEXT, CourseName TEXT);

After the successful creation of a table, eight records are inserted into it. The records are as follows:

INSERT INTO Student(StdName, StdDegree, CourseName)
VALUES
('test1','att1','val1'),
('test1','att2','val2'),
('test1','att3','val3'),
('test1','att4','val4'),
('test2','att1','val5'),
('test2','att2','val6'),
('test2','att3','val7'),
('test2','att4','val8');

Once the table is created and records are added, the SELECT statement can be used to view the current condition of the table.

Select * from Student;

The snapshot of the table is as follows:

Now we can move on to the definition and usage of the two functions that help transpose columns to rows in PostgreSQL.

Use the crosstab() Function to Transpose Columns to Rows in PostgreSQL

Let us discuss what the crosstab() function is.

This function is supported by PostgreSQL version 9.7 and above. It can be used in several ways with different arguments.

The syntax of the crosstab() function is as follows:

1. crosstab(sql text)
2. crosstabN(sql text)
3. crosstab(source_sql text, category_sql text)
4. crosstab(sql text, N integer)

The working and description of each argument are explained in the documentation of PostgreSQL in detail.

For this article, we only require crosstab(sql text). The argument in crosstab(sql text) is an SQL query.

How the crosstab() Function Works

This section focuses on the workings of the crosstab() function. An example is used to understand the concept of the crosstab() function.

Here is a query that has been written with the help of the crosstab() function:

SELECT *
FROM crosstab(
  'select StdName, StdDegree, CourseName
   from Student
   where StdDegree = 'att2'
   or StdDegree = 'att3'
   order by 1,2')
AS Student(row_name text, category_1 text, category_2 text, category_3 text);

This is easier to understand with the concept of nested queries. The query in quotation marks is the inner query that also works as an argument for the crosstab() function; it is executed at first.

The query in the argument returns records where the StdDegree has the value att2 or att3. This is shown by the crosstab()function with the headers row_name, category_1, category_2, and category_3.

The result of the example is as follows:

Use the unnest() Function to Transpose Columns to Rows in PostgreSQL

Let us discuss about the unnest() function now. The unnest() function is an array function supported by version 9.7 and above of the PostgreSQL server.

There are numerous other array functions offered in PostgreSQL that are mentioned in the documentation of the server.

The syntax of the unnest() function is as follows:

unnest(anyarray)

The unnest() function takes an array, or a set of arrays, as an argument. It expands the array into separate records and displays the result to the user.

The unnest() function supports single and multi-dimensional arrays.

How the unnest() Function Works

This section focuses on the workings of the unnest() function. An example is used to understand the concept of the unnest() function.

The unnest() function can be used in two different ways. Example 1 mentions the first way unnest() can be used to transpose columns to rows.

SELECT unnest('{Std_no, StdName, StdDegree, CourseName}'::text[]) AS col
	, unnest('{1,test1,att1,val1}'::text[]) AS row1
	, unnest('{2,test1,att2,val2}'::text[]) AS row2
	, unnest('{3,test1,att3,val3}'::text[]) AS row3
	, unnest('{4,test1,att4,val4}'::text[]) AS row4;

The query uses the unnest() function that takes an array as an argument and returns separate records. Therefore, the result of the query transposes columns to rows:

The query mentioned in Example 1 can be written in a slightly different format. Both examples have similar queries that perform the same task of converting columns to rows.

Here is a different format for the query mentioned in example 1:

SELECT * FROM unnest
    (
     '{Std_no, StdName, StdDegree, CourseName}'::text[]
     ,'{1,test1,att1,val1}'::text[]
     ,'{2,test1,att2,val2}'::text[]
     ,'{3,test1,att3,val3}'::text[]
     ,'{4,test1,att4,val4}'::text[]
	)
 AS t(col,row1,row2,row3,row4);

The difference between the two examples can be seen. In the first query mentioned in Example 1, each unnest() function catered to a single-dimension array.

Each unnest() function has a separate header in example 1.

In this example, only one unnest() function has been used that caters to a multi-dimensional array. The headers for each of the new columns are mentioned at the end.

The query generates the same result as Example 1:

Conclusion

Different functions and SQL statements can be tested to transpose columns to rows. When transposed, each row transforms into a new column.

This dynamic result makes it difficult for a single query to transpose columns into rows. Hence, the crosstab() and unnest() functions are used to help with such problems.