How Can I Pivot a Column in SQL Using CASE WHEN and Aggregation?

Use SQL’s CASE WHEN statement to pivot columns

In relational databases, in order to analyze or display data, it is often necessary to reshape data into different formats. One common transformation is pivoting, which combines multiple rows of data into a single row.

Question:

Consider a table named "Bank" with columns "name", "val" and "amount":

name	val	amount
John	1	2000
Peter	1	1999
Peter	2	1854
John	2	1888

The goal is to pivot the "val" column and create two new columns named "amountVal1" and "amountVal2", representing the "amount" values ​​with "val" values ​​of "1" and "2" respectively. The desired output looks like this:

name	amountVal1	amountVal2
John	2000	1888
Peter	1999	1854

Solution:

In order to perform this conversion, CASE WHEN expressions can be used along with aggregate functions. The following SQL query achieves the expected results:

<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>

In this query:

• CASE WHEN expression retrieves the "amount" value for each "val" and converts any unmatched "val" value to 0.
• The SUM() function aggregates the results for each "name", effectively adding the "amount" values ​​corresponding to the "val" values.
• The GROUP BY clause groups the results by "name", ensuring that values ​​are merged for each unique name in the table.

By using this query, you can successfully pivot the data in the "Bank" table, creating the required columns "amountVal1" and "amountVal2" which contain "val" values ​​of "1" and "2" respectively "amount" value.

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