How to Pivot a Column in SQL Using CASE WHEN and SUM()?

Using CASE WHEN and SUM() to Pivot a Column in SQL

Data transformation often requires reshaping tables, moving data from rows into columns—a process known as pivoting. This example demonstrates how to pivot a column in SQL using CASE WHEN and SUM().

Let's consider a sample table named "Bank":

<code class="language-sql">Bank:
name  val   amount
John   1     2000
Peter  1     1999
Peter  2     1854
John   2     1888</code>

Our goal is to transform this table into the following format:

<code class="language-sql">name  amountVal1 amountVal2
John    2000        1888
Peter   1999        1854</code>

Why a Simple CASE WHEN Fails

A naive approach using only CASE WHEN might look like this:

<code class="language-sql">SELECT name,
CASE WHEN val = 1 THEN amount ELSE 0 END AS amountVal1,
CASE WHEN val = 2 THEN amount ELSE 0 END AS amountVal2
FROM bank;</code>

This produces an incorrect result:

<code class="language-sql">name  amountVal1 amountVal2
John    2000        0
Peter   1999        0
John    0          1888
Peter   0          1854</code>

The problem is that each row is treated independently. We need to aggregate the results.

The Correct Solution with SUM()

The solution lies in using the SUM() aggregate function to sum the amount values for each name and val combination:

<code class="language-sql">SELECT 
  name,
  SUM(CASE WHEN val = 1 THEN amount ELSE 0 END) AS amountVal1,
  SUM(CASE WHEN val = 2 THEN amount ELSE 0 END) AS amountVal2
FROM bank
GROUP BY name;</code>

This query correctly produces the pivoted table:

<code class="language-sql">name  amountVal1 amountVal2
John    2000        1888
Peter   1999        1854</code>

The GROUP BY name clause ensures that the SUM() function aggregates the amounts for each unique name. The CASE WHEN statements conditionally assign amounts to the appropriate columns (amountVal1 and amountVal2), handling cases where a particular val is absent for a given name by using 0 as the default value. This technique provides a concise and efficient way to perform column pivoting in SQL.

The above is the detailed content of How to Pivot a Column in SQL Using CASE WHEN and SUM()?. For more information, please follow other related articles on the PHP Chinese website!