How to Pivot a SQL Column Using CASE WHEN to Transform Row Data into Columns?

Using CASE WHEN for SQL Column Pivoting

Need to transform your SQL data from rows to columns? The CASE WHEN statement offers a straightforward solution for pivoting, especially when dealing with tables containing duplicate values in a column that need aggregating.

Let's illustrate with an example. Imagine this table:

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

Our goal: restructure this into a table where unique "val" values become columns, and each unique "name" is a row. The desired output:

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

Here's how to achieve this pivot using SUM() and CASE WHEN:

<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 uses SUM() to aggregate amount values for each unique name and val combination. The CASE WHEN statement checks if val is 1 or 2, assigning the corresponding amount accordingly. GROUP BY name ensures one row per unique name. The result? amountVal1 and amountVal2 columns neatly represent the pivoted data. This method is effective for a known, limited number of val values. For a dynamic number of values, consider using PIVOT or other more advanced techniques.

The above is the detailed content of How to Pivot a SQL Column Using CASE WHEN to Transform Row Data into Columns?. For more information, please follow other related articles on the PHP Chinese website!