How to Correctly Pivot Columns in SQL Using CASE WHEN and GROUP BY?

Pivot SQL columns using CASE WHEN and correct GROUP BY statement

Problem description:

To pivot a column in a table into multiple columns based on specific conditions, SQL queries can use CASE WHEN expressions. However, when implementing this method, users may experience incorrect results due to missing grouping.

Query:

To resolve this issue, a modified query including the SUM() aggregate function and correct grouping is provided below:

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

Explanation:

• The SUM() function should be used in the CASE WHEN expression to calculate the total amount of each val (1 and 2) for each name.
• The GROUP BY name clause groups the results by the name column, ensuring that amounts for each different name are summed correctly.

Expected results:

This improved query will produce the desired output:

name	amountVal1	amountVal2
John	2000	1888
Peter	1999	1854

The above is the detailed content of How to Correctly Pivot Columns in SQL Using CASE WHEN and GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!