Advanced SQL — Reshaping Tables

Reshaping tables is a crucial aspect of data analysis, and it involves transforming a table from one format to another. Two of the most common ways of reshaping tables are from long to wide and from wide to long. SQL provides a powerful set of tools for performing these transformations.

Image credit to Unsplash

In the long format, each row represents a single observation or measurement, and there are typically multiple columns representing different attributes or variables of that observation. In the wide format, each row represents a single unit or entity, and there are multiple columns representing different measurements or attributes of that unit.

Reshaping tables from long format to wide format or wide format to long format is a highly frequent data manipulation requirement. Knowing the different ways to reshape a table from long2wide or wide2long can be a time saver.

Long to Wide

Long to wide table transformation involves converting a table with multiple rows per group into a table with a single row per group. This transformation is useful for presenting data in a more compact format, making it easier to compare values across different groups.

For example, you have a table Client with three columns: name, category, and value. Where category has different attributes such as sports, color, and bonus.

You want to reshape the table from long to wide to see each individual’s measurement.

select 
  name, 
  max(case when category = 'sport' then value else null end) as sport, 
  max(case when category = 'color' then value else null end) as color, 
  max(case when category = 'bonus' then value else null end) as bonus
from Client
group by name

The max() function ignores all null values in the expression. Therefore, all values under the same category will be distributed to the according column. This is the result.

Wide to Long

Wide to long table transformation involves converting a table with multiple columns per group into a table with multiple rows per group. This transformation is useful for presenting data in a more granular format, making it easier to analyze trends over time or across different categories.

For instance, you have a wide table with the same columns from last exercise. You want to reshape it from wide to long.

select 
 name, 
 'sport' as category, 
 sport as value
from wideClient
union all 
select 
 name, 
 'color' as category, 
 color as value
from wideClient
union all
select 
 name, 
 'bonus' as category, 
 bonus as value 
from wideClient

The union all syntax here concatenate additional rows to the previous table. This is the reshaped long table format.

Other Solutions

Depending on your database, there might be unpivot or pivot functions you can use to reshape the table. The above demonstrated solutions are what I use the most.

I hope you learned something new about reshaping tables. Leave a comment and let me know what is your go to functions when you reshape tables.

I am Kai Cui, a humble earther. I write about business intelligence and sustainability. Join my network on Linkedin here to get real-time access to my content.