Page tree
Skip to end of metadata
Go to start of metadata

Table of contents


Pivoting from rows to columns

Let's consider you have a list of roles, and you want to move it into an array with ticks:

The initial data (on the left) comes from the following query:

SELECT Name, Role FROM Roles

Step 1. Create a separate column for each category

We'll use the keyword CASE, which works as follow: "IF the column Role is equal to 'Parent', THEN display 'X'". If the value isn't 'Parent', it displays nothing in that column.

SELECT
    Name,
    CASE Role WHEN 'Parent' THEN 'X' END "Role: Parent",
    CASE Role WHEN 'Provider' THEN 'X' END "Role: Provider"
FROM Roles

Here's the result:

Step 2. Collapse groups of rows with the same name

In the results above, Brian Carter appears twice, because he's both a parent and a provider.

We can collapse his two rows into a simple one. The MAX keyword takes the biggest cell of a column. We can restrict the scope of the MAX using "GROUP BY Name": It will select the max of the cells of the group. In Brian's case, MAX("Role: Parent") will aggregate the row with a X and the row with the NULL value. NULL is always weaker than a value, so only X will remain. We'll do the same for the other column and here's the result:

Final query

We've:

  • Created one column for each category
  • For each name, aggregate all roles using GROUP BY "Name" and MAX().

Here's the result:

SELECT Name, MAX(CASE Role WHEN 'Parent' THEN 'X' END) "Role: Parent", MAX(CASE Role WHEN 'Provider' THEN 'X' END) "Role: Provider"
FROM (
    SELECT Name, 'Parent' Role FROM Parents
	UNION ALL
	SELECT Name, 'Provider' Role FROM Providers
) Roles
GROUP BY Name

 

Pivoting a large row into a single column

Sometimes we want to display the detail of one record, and we want to transform a long row into a single column:

 

Here's the query which generates the first table:

SELECT * FROM Customers

The solution is actually built-in in Postgres (Users of HSQL won't be able to use it): In Postgresql, you can build a structure named "Array" which is a single value composed of several elements, the you can expand the array over separate rows. In the following example, the first column is the array of names that we expand over 6 rows, and the second column contains the values of the six names:

SELECT 
 unnest(array['Contact Name', 'Company', 'Address', 'Tax ID', 'Rating', 'Localization', 'Number of Employees']) "Field",
 unnest(array[contact_name, company, address, tax_id, rating, localization, number_of_employees]) "Value"
FROM Customers

 

(tick) Done, you know how to pivot columns into lines and opposite.

 

 

  • No labels