How Can I Pivot a SQL Table Using CASE WHEN to Avoid Duplicate Rows?

Use the CASE WHEN statement in SQL to perform column pivoting

The pivot operation in SQL refers to converting vertically arranged columns in the data table into horizontally arranged columns. This is usually accomplished using the CASE WHEN statement.

Suppose there is a table with three columns: name, value and amount. The goal is to transform the data into a new representation: each row represents a name and each column represents a specific value in the val column, such as 'Val1' and 'Val2'.

You have tried writing an initial query using a CASE WHEN statement, but the results are not quite correct. Specifically, the rows for John and Peter are repeated twice, with one row containing the amount value associated with val = 1 and the other row containing the amount value associated with val = 2.

To solve this problem, we can use the SUM() aggregate function in conjunction with the CASE WHEN statement. By summing the amounts associated with each val value, we effectively group the rows for each name and merge the values ​​into a single column.

The following modified query will produce the desired results:

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

This query sums the amount of each val value in each name group and gets the following output:

name	amountVal1	amountVal2
John	2000	1888
Peter	1999	1854

The above is the detailed content of How Can I Pivot a SQL Table Using CASE WHEN to Avoid Duplicate Rows?. For more information, please follow other related articles on the PHP Chinese website!